With the help of this forum, I am trying to correctly design my tables to
be
normalized. I would like to break my design down into sections to work on
just for ease of discussion and for the rest of people trying to figure
this
all out it may make it clearer to them also. I am a beginner in Access and
TRYING HARD to figure it out so that I can properly design a database
instead
of the 55 field non-normlized one I currently use(:-0
Our non-profit organization has instructors. They may have many addresses
and therefore address types. Here are the table designs so far:
One instructor can have many addresses
One address can have many instructors
Therefore this is a many to many realtion****p, and so will be solved with
a
junction/intermediate table (called instructorAddresses)
tblInstructors
InstructorID (autonumber, primary key,Long Integer, ref. integrity and
cascade updates)
SpecialID# (text field as this badge number starts with a series of
zeroes)
FirstName (text field)
MidName (text field)
LastName (text field)
*** (or should this be a lookup table? If so, what relation****p do I
create
and where? I know some of you are saying "well an instructor can only be
one
*** at a time" which is true, but what do you do if an instructor is also
transgendered and you need to keep a history of when that instructor was
male, and when they were female)
tblAddresses
AddressID (autonumber,primary key,Long Integer, ref. integrity and cascade
updates)
AddressLine1
AddressLine2
AddressLine3
City
PostalCode
Prov*****ateCounty
Country
tblInstructorAddresses (the junction or intermediate table)
InstructorAddressID (autonumber,primary key,Long Integer, ref. integrity
and
cascade updates)
InstructorID (Foreign Key, number, Long integer, Duplicates OK,)
addressID (Foreign Key, number, Long integer, Duplicates OK,)
I have a one to many relation****p created from tblInstructors to
tblInstructorAddresses
I have a one to many relation****p created from tblAddresses to tbl
InstructorAddresses
Now, I obviously need an addressType Table (Home, Work, ****pping,Billing
etc):
tblAddressType
AddressTypeID (autonumber,primary key,Long Integer, ref. integrity and
cascade updates)
AddressType (Text )
FromDate (date)
ToDate (date)
One Address can have many address types (i.e 123 maple Ave can be home and
****pping and billing)
One AddressType has many Addresses (i.e the ****pping address type can vary
between a home address or a school address for one instructor depending on
the day)
Am I right that this is a many to many relation****p, or am I wrong that
AddressType has many addresses? Am I modeling this correctly as a many to
many relation****p?
Billiam
|
