[AccessD] Normalizing issue

Susan Harkins ssharkins at bellsouth.net
Thu Jun 23 14:38:59 CDT 2005


Jim, I've already gone with separate tables because I got to thinking -- as
this things grow I may run into other data that doesn't fit both books and
magazines -- 

Right now, we're just kind of playing with this. But if it works well and is
flexible, a lot of different regions could end up using it -- so the more
problems I resolve now, the better. 

Susan H. 

Susan,

 <<Books are usually in or out of print and that just doesn't apply for
articles. If I use a Yes/No for InPrint, then I've got records when an
inappropriate value -- No for magazines isn't really correct. I could use a
tri-state Yes/No I guess, and put Null to work, but ug... >>

  Not understanding the publishing business, I'm not sure I fully understand
what the problem is.

  But I guess it depends on how hung up you are on following rules.  You can
go the simple route and have one table for publications and include the
field InPrint and inherent in the interface is that fact that it only
applies to books (ie. you hide the control if it's not a book).  That's
basically a no-no as you said, as all fields really don't pertain to what
your supposed to be describing (a publication).

  The next step would be to have a separate table for each publication type;
one for books, one for mag's, video's etc.  Then each table only contains
the fields that pertain to the type of thing your trying to describe.

  Last you can go all out and describe the object, it's attributes, and the
values for those attributes.  For example:

tblPublicationTypes - List of all possible publication types (book, mag,
article, documentary video, etc).
PubTypeID
Description

tblAttributes - List of all possible attributes that any publication might
have.
AttribID - PK
Description

tblPubTypeAttributes - One record for each PubType / Attribute Combination -
So this is a list of attributes for a specific type of publication.
PubTypeAttribID - PK
PubTypeID - FK-A
AttribID - FK-B

tblPublications - one record per publication PubID - PK PubTypeID - FK Name

tblPublicationsValues - One record for each attribute for the type of
publication that holds the actual value.
PubValueID - PK
PubID - FK-A
PubTypeAttribID - FK-B
Value


  An example:

tblPublicationTypes

PubTypeID       Description
1               Book
2               Mag
3               Video


tblAttributes

AttribID  Description
1         Title
2         Date Created
3         In Print
4         Media format
5         Author
6         Publication Date
7         Director


tblPubTypeAttributes

PubTypeAttribID     PubTypeID      AttribID
1                      1              1
2                      1              2
3                      1              3
4                      1              5
5                      1              6
6                      2              1
7                      2              2
8                      2              5
9                      2              6
10                     3              1
11                     3              2
12                     3              4
13                     3              6
14                     3              7


  So books have the attributes: Title, Date Created, In Print, Author, and
publication date.
  Mag has: Title, Date Created, Author, Publication date.
  Video has: Title, Date Created, Media Format, Publication Date, and
Director.


tblPublications - one record per publication
PubID          PubTypeID
1                 1
2                 1
3                 2
4                 3
5                 3

  So we have 2 books, 1 mag, and 2 videos listed.

tblPublicationsValues
PubValueID        PubID        PubTypeAttribID                     Value
1                   1                1                  "Jim's First Access
Book"
2                   1                2                  06/15/05
3                   1                3                  Yes
4                   1                4                  "JND"
5                   1                5                  06/18/05
6                   2                1                  "Jim's Second Access
Book"
7                   2                2                  06/19/05
8                   2                3                  No
9                   2                4                  "JND"
10                  2                5                  <NULL>
11                  3                6                  "Spy vs Spy"
12                  3                7                  02/01/05
13                  3                8                  "JRD"
14                  3                9                  02/10/05
15                  4                10                 "eXtreme Skiing"
16                  4                11                 01/01/00
17                  4                12                 "AVI"
18                  4                13                 01/01/01
19                  4                14                 "Spielberg"
20                  5                10                 "Sky Diving For
beginers"
21                  5                11                 01/01/99
22                  5                12                 "35 mm"
23                  5                13                 01/01/00
24                  5                14                 "JK Rowling"


  Whew...think I got all that right.  This design is the ultimate in
flexability, but certainly the most complex to implement.  Without any
changes in programming, you can add a new type of "thing" to the database
without breaking any normalization rules.  Then add instances of those
things and assign values to the attributes of them.

  Two notes:

1. Some might also add to the attribute table or as a seperate table a range
of possible values that an attribute can have.
2. They layout above is for someone who believes in surrogate keys.  I
wouldn't do it that way myself, but simplify a bit.

Jim.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan Harkins
Sent: Thursday, June 23, 2005 2:28 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Normalizing issue


This is really getting much more complicated than I had planned. Now I have
to figure out how to deal with books versus articles. :) I thought about one
table that id's each publication as a book, article, etc. but I have one
problem.

Books are usually in or out of print and that just doesn't apply for
articles. If I use a Yes/No for InPrint, then I've got records when an
inappropriate value -- No for magazines isn't really correct. I could use a
tri-state Yes/No I guess, and put Null to work, but ug...

I can have a separate table that stores only book InPrint field. Or, I can
just use two different tables, one for books, for mags.

Susan H.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Thursday, June 23, 2005 1:23 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Normalizing issue

<<OK -- so you're relating the pen name table to the member name and then
relating the book table to the pen name table?>>

  Yes.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan Harkins
Sent: Thursday, June 23, 2005 1:13 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Normalizing issue


OK -- so you're relating the pen name table to the member name and then
relating the book table to the pen name table?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Thursday, June 23, 2005 1:10 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Normalizing issue

Slight correction:

tblMembers
MemberID
MemberName
DateCreated

tblPenNames
PenID
MemberID
PenName
DateCreated
Bio

tblBooks
BookID
PenID

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan Harkins
Sent: Thursday, June 23, 2005 12:58 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Normalizing issue


I've been considering this, this morning and I think what I've finally come
up with is this:

A member name -- that's their real name -- which will relate to their books.
I "think" I will add a pen name field to the book table -- if they wrote the
name under another name, it'll be there for searches, write-up's, whatever.
But, there's only going to be one pen name for each book, so I think it's
safe to do this. What do you think?

Susan H.

Since it's a writer's database, a separate *table* for pen names makes a lot
of sense.  Some writers use multiple pen names, so creating a pseudonym
table is the safest way to handle that and relate it to the writer's actual
name.  You could always use the real name as the default when filling in a
pen name.

Charlotte Foust


-----Original Message-----
From: Susan Harkins [mailto:ssharkins at bellsouth.net]
Sent: Thursday, June 23, 2005 8:43 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Normalizing issue



If it isn't a middle or maiden name, what is it?  This gets even more tricky
with non-English names where "last" names can get really confusing.  I
generally provide a field for first, middle, and last name and make it the
responsibility of the user to enter the appropriate value in the appropriate
field.  The rules for parsing this can become so draconian, that it hardly
seems worth the work to do anything else.

========I'm in the middle of creating a database (pro bono) for a writer's
organization. It's a simple deal and will require little work
-- but this is already cropping up. These people will want their "writing"
names with their publications -- I'm thinking about just adding one more
field to store their "pen name" or whatever -- but then that becomes a
nuisance if their name and professional name are the same
-- gotta fill in both or allow for the blanks and go find the real name
-- such a nuisance --

I think at this point the middle name is the best solution --

Susan H.

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list