Is this structure normalized??

[Deb Smith]

The following are 5 tables from a larger data base that as a start, I want
to ensure are normalized and/or are structured to provide maximum
flexibility.

I thought this data base was almost finished (80% completed), but the
end-user has now requested that additional information and features be
added. This is causing me many problems. I therefore am back at the
beginning trying to make sure the database is structured appropriately. Once
I know this, at least I will be able to separate out my inexperience in
designing queries, forms, coding, etc, vs issues with the overall database
design.

Input, advice, suggestions, criticisms, anything regarding this structure.
would be greatly appreciated.

tblPerson
- PersonID (PK- autonumber)
- LName
- FName
- MailingListNameID (FK - tblMailList.MailingListNameID)

There are other fields in tblPerson that are FK to other tables, but I am
not sure they are relevant at this time

tblMailList
- MailingListNameID (PK-autonumber)
- MailingListName1 (txt)
- Selected (Yes/No)

tblEventMailList
- MailingListNameID (compositePK- tblMailList.MailingListNameID)
- EventID (compositePK -tblEventInfo.EventID)
- PersonID (FK - tblPerson.PersonID)
- Invited (Y/N)
- NumberInvited(Number)
- LabelRequired (Y/N)

tblAttendance
- EventID (compositePK -tblEventInfo.EventID)
- PersonID (compositePK - tblPerson.PersonID)
- MailingListNameID (FK - MailingListName

tblEventInfo
- EventID (PK -autonumber)
- EventDate (date)
- OccassionID (FK -tblOccassionType.OccassionID)
- EventActivityID (FK - tblEventActivity.EventActivityID)
- EventLocationID (FK - tblEventLocation.EventLocationID)
- Details (txt)

Other background info.
The person table is used to collect basic information about the individual.
Since more than one person can share a mailing list name, a separate table
was created called MailList. (For example; Jane Doe PersonID 1 and John Doe
PersonID2 can share the MailListName Mr. and Mrs. John Doe. Jane and John
Doe would obviously share MailingListName1.

I need a means to link people, mailing list names and events together and
then to generate a mailinglist and labels. I therefore created tbl
EventMailList. EventMailList is a junction table that links tblMailList and
tblEventInfo both in a one to many relationship since there can be many
events that a mailinglist name can be included/invited to.

TbleAttendance is used to track attendance status of each individual invited
to an event.

Thanks to all for providing input.

 

[Immanuel Sibero]

Hi Deb,

The way I see it, you have the following main Entities:

- Person (tblPerson), PersonID PK
- MailList (tblMailList), MailListID PK
- Event (tblEvent), EventID PK

The following are junction tables to *glue* (i.e. relate) the above
Entities:

- Person and MailList are probably related in many-to-many fashion, you
would need a junction table, say, tblPersonMailList which contains two FK's
from tblPerson and tblMailList (i.e. PersonID, MailListID)
- MailList and Event are probably related in many-to-many fashion, you would
need a junction table, say, tblEventMailList which contains two FK's from
tblEvent and tblMailList (i.e. EventID, MailListID)
- Person and Event are probably related in many-to-many fashion, you would
need a junction table, say tblAttendance as you call it, which contains two
FK's from tblPerson and tblEvent (i.e. PersonID, EventID)


In general, that's it! You can see how identifying *Entities* first and then
identifying the *Relationships* later helps you come up with a good design.
Several problems I see from your post:

- They way you relate Person and MailList is many-to-one, which would not
work according to your stated requirement. One person can not be in multiple
MailList.
- PersonID should not be an FK in tblEventMailList. It's redundant. Between
a MailList and an Event, all you care about is which MailList is used for
what Event. Whether a Person is involved depends on if that Person is in the
MailList in question. So you dont need to keep track of which Person is
involved in tblEventMailList.
- In a similar fashion, you dont need MailListNameID in tblAttendance. It's
redundant. In this case, you can resolve MailLists involved in this event
through PersonID.


This phase of identifying *Entities first and then Relationships* is crucial
since a flawed design will haunt you later. Users will want to add features,
functionalities, etc., some of which can only be done if the Entities and
Relationships had been designed correctly.


HTH,
Immanuel Sibero

 

[Deb Smith]

Immanuel

Thank you so much for your insightful and helpful comments. I am now trying
to work with the structure you have suggested. I am hopeful, it will solve
some of the issues I have been having.
The issue for me has been what you call tblMailList. The MailList table I
had structured was really only a means to capture a unique mailing list
name. What I was missing was the link between person and the actual creation
of a mail list.

Once again thanks for taking the time and thought to respond so effectively.
If I have any other problems, I will post them later.

Deb

 

[Immanuel Sibero]

Once again thanks for taking the time and thought to respond so effectively.
If I have any other problems, I will post them later.

You're welcome. Please do post back whenever you have problems. I myself
have benefited from the MVP's and experts in this newsgroup. Good luck.

Immanuel Sibero