If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Address Design Structure
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 realtionship, 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) Sex (or should this be a lookup table? If so, what relationship do I create and where? I know some of you are saying "well an instructor can only be one sex 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 ProvinceStateCounty 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 relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses Now, I obviously need an addressType Table (Home, Work, Shipping,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 shipping and billing) One AddressType has many Addresses (i.e the shipping 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 relationship, or am I wrong that AddressType has many addresses? Am I modeling this correctly as a many to many relationship? Billiam |
#2
|
|||
|
|||
Address Design Structure
Hi Billiam
First your question about sex/gender. Sometimes one must ask the question "why?" I know that it is technically possible for one of your instructors to start out male and them become female, but before adding significantly to the complexity of your database design, you need to ask yourself if this really matters to your business model, and whether it is necessary to keep a history of the dates of the changes. If you decide it really is important to track the history then you need a table InstructorGenderHistory: ighInstructor (FK to Instructors) ighGender (*see below) ighStartDate (date/time) Note that you don't need an end date. The current gender (your most common query) is the record with the latest start date and the gender at a given date in the past is the record with the latest start date BEFORE the given date. The actual gender code could be a simple M or F, or it could be a foreign key to a reference table (lookup tables have bad connotations!). Often it is useful to have a static table of other gender-related terms, such as pronouns (he/she, him/her, his/hers, etc). If you don't want to keep the history then put this gender code field directly in your Instructors table (plain M/F or FK as required). On the address question, the correct place for your AddressType FK field is in the junction table (tblInstructorAddresses). Say you have two instructors: 1=John and 2=Mary. They live together at 123 Maple Avenue (AddressID 1) John works at 456 Elm Street (AddressID 2) Mary works at 789 Oak Street (AddressID 3) Your junction table has these fields: iadID (optional primary key) iadInstructor (FK to tblInstructors) iadAddressType (simple code H/W/S/B or FK to tblAddressTypes) iadAddress (FK to addresses) iadStartDate (only if you need to keep address change history) Your data looks like this: (1) 1 H 1 (John lives at address 1) (2) 2 H 1 (Mary lives at address 1) (3) 1 W 2 (John works at address 2) (4) 2 W 3 (Mary works at address 3) The address type field is like the gender field - it's up to you whether you have a reference table or keep it as a simple one-letter code. Whatever you decide, the history tracking dates DO NOT belong in your reference table, but in the junction table. If you want an instructor to have at most ONE address of each type, then make a unique composite key of iadInstructor and iadAddresstype. Just a point on cascading relationships - it often makes sense to have cascading deletes between a main table and a junction table, but it never makes sense to have cascading updates on an autonumber field, because it is impossible to update an autonumber field. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... 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 realtionship, 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) Sex (or should this be a lookup table? If so, what relationship do I create and where? I know some of you are saying "well an instructor can only be one sex 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 ProvinceStateCounty 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 relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses Now, I obviously need an addressType Table (Home, Work, Shipping,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 shipping and billing) One AddressType has many Addresses (i.e the shipping 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 relationship, or am I wrong that AddressType has many addresses? Am I modeling this correctly as a many to many relationship? Billiam |
#3
|
|||
|
|||
Address Design Structure
On Thu, 31 Jul 2008 12:35:00 -0700, Billiam
wrote: One instructor can have many addresses One address can have many instructors This is kind of a peculiar situation. Could you provide some real world examples of one address with many instructors? I have a one to many relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses I think you may be over-normalizing your database. While repeated fields are usually frowned on, multiple address lines for one instructor would not typically be out of line. Lynn Trapp www.ltcomputerdesigns.com |
#4
|
|||
|
|||
Address Design Structure
I offered to help you on July 24 and you chose to listen to Klatuu instead.
You did not get the help you need in the thread on July 24 so here you are a week later and no further ahead. What are you going to do if you do not again get the help you need in this thread? Steve "Billiam" wrote in message ... 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 realtionship, 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) Sex (or should this be a lookup table? If so, what relationship do I create and where? I know some of you are saying "well an instructor can only be one sex 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 ProvinceStateCounty 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 relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses Now, I obviously need an addressType Table (Home, Work, Shipping,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 shipping and billing) One AddressType has many Addresses (i.e the shipping 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 relationship, or am I wrong that AddressType has many addresses? Am I modeling this correctly as a many to many relationship? Billiam |
#5
|
|||
|
|||
Address Design Structure
"Steve" wrote in message
m... I offered to help you on July 24 and you chose to listen to Klatuu instead. You did not get the help you need in the thread on July 24 so here you are a week later and no further ahead. What are you going to do if you do not again get the help you need in this thread? Steve Ah, solicitation by intimidation. So is this your new tactic? These newsgroups are provided by Microsoft for FREE peer to peer support, not as a source for lining your pockets. Isn't it time for your disappearing act? For the past few years you have been trolling the newsgroups and then vanishing near the end of July only to reappear the following June. John... Visio MVP |
#6
|
|||
|
|||
Address Design Structure
examples in our organization include:
Husband Wife and child, all instructors, at the same home address, and any combination of work, billing and shipping addresses. Also, many instructors at one school is another scenario. Our business rules allow a maximum of 5 street addresses, and most use 4 addresses. "Lynn Trapp" wrote: On Thu, 31 Jul 2008 12:35:00 -0700, Billiam wrote: One instructor can have many addresses One address can have many instructors This is kind of a peculiar situation. Could you provide some real world examples of one address with many instructors? I have a one to many relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses I think you may be over-normalizing your database. While repeated fields are usually frowned on, multiple address lines for one instructor would not typically be out of line. Lynn Trapp www.ltcomputerdesigns.com |
#7
|
|||
|
|||
Address Design Structure
"John... Visio MVP" wrote:
Husband and wife? Father and daughter? Either way, I would keep a one to one relationship between address and instructor. They may seperate or the daughter leave home. John... Visio MVP Yes you are right, John, we have many groups of families that are instructors, sharing the same home address, but with any combination of Billing, shipping, and work addresses. In addition, groups of teachers teach at a school. Sorry I did not clarify this up front. And you hit the nail on the head--I will have changes when a spouse or child leaves the home address, or one of the school addresses change....I was hoping this could be managed by an effective date??? It is also necessary to keep a history of the addresses, which was my next question. |
#8
|
|||
|
|||
Address Design Structure
Thank you again for your offer, Steve. This free forum, however, suits my
limited budget as a non-profit org. In addition, I think the real point is that I do have a working non-normalized database, I am here to learn how to use Access properly so that I can really unleash its huge potential. Sometimes the best learned lessons are the ones that you have to wrangle with over time, and I have found that the very knowledgeable and talented people here ( who do offer their services for free ) to be very helpful,and thought provoking. I certainly appreciate their efforts and applaud their dedication to what must sometimes be a very frustrating experience for them dealing with us newbies! Billiam "Steve" wrote: I offered to help you on July 24 and you chose to listen to Klatuu instead. You did not get the help you need in the thread on July 24 so here you are a week later and no further ahead. What are you going to do if you do not again get the help you need in this thread? Steve "Billiam" wrote in message ... 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 realtionship, 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) Sex (or should this be a lookup table? If so, what relationship do I create and where? I know some of you are saying "well an instructor can only be one sex 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 ProvinceStateCounty 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 relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses Now, I obviously need an addressType Table (Home, Work, Shipping,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 shipping and billing) One AddressType has many Addresses (i.e the shipping 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 relationship, or am I wrong that AddressType has many addresses? Am I modeling this correctly as a many to many relationship? Billiam |
#9
|
|||
|
|||
Address Design Structure
On Fri, 1 Aug 2008 08:02:10 -0700, Billiam
wrote: examples in our organization include: Husband Wife and child, all instructors, at the same home address, and any combination of work, billing and shipping addresses. Also, many instructors at one school is another scenario. Our business rules allow a maximum of 5 street addresses, and most use 4 addresses. The possible need for up to 5 street addresses is definitely reaching the point where you would need to separate them into a separate table. I would recommend that you use the approach that Graham Mandeno has outlined for you. Lynn Trapp www.ltcomputerdesigns.com |
#10
|
|||
|
|||
Address Design Structure
Thank you very, very much for your detailed and timely answer, Graham! I have
made some comments/asked questions as noted below, if you do not mind sticking with me a bit longer on this? "Graham Mandeno" wrote: Hi Billiam First your question about sex/gender. Sometimes one must ask the question "why?" I know that it is technically possible for one of your instructors to start out male and them become female, but before adding significantly to the complexity of your database design, you need to ask yourself if this really matters to your business model, and whether it is necessary to keep a history of the dates of the changes. It is not a technical possibility but a fact, and I do need to track the history as the person is submitting to a Government office. While I do not believe it is likely to happen with another instructor, what do you think is the best way to handle/link Patrick/Patricia's (not real name) records since the first name and middle names have also changed, and I have to produce reports of this person's Instructor Activity as either male or female. If you decide it really is important to track the history then you need a table InstructorGenderHistory: ighInstructor (FK to Instructors) ighGender (*see below) ighStartDate (date/time) If I understand you correctly, we are treating this as a many to many relationship, and resolving that with the junction table InstructorGenderHistory. So one instructor can have many genders (as in Pat/Patricia) One gender can have many Instructors. What was/is confusing me is that really an instructor can only be one sex at a time, so wouldn't that make it a one to one relationship??? And if so, all I would do is have a gender reference table with a Start date field? Note that you don't need an end date. The current gender (your most common query) is the record with the latest start date and the gender at a given date in the past is the record with the latest start date BEFORE the given date. Okay, that makes sense. The actual gender code could be a simple M or F, or it could be a foreign key to a reference table (lookup tables have bad connotations!). Do you mean they sometimes cause confusion between lookup fields and lookup tables? Often it is useful to have a static table of other gender-related terms, such as pronouns (he/she, him/her, his/hers, etc). Yes, this reference table is exactly what I would like, and the personal pronouns are a great idea. On the address question, the correct place for your AddressType FK field is in the junction table (tblInstructorAddresses). Say you have two instructors: 1=John and 2=Mary. They live together at 123 Maple Avenue (AddressID 1) John works at 456 Elm Street (AddressID 2) Mary works at 789 Oak Street (AddressID 3) Your junction table has these fields: iadID (optional primary key) iadInstructor (FK to tblInstructors) iadAddressType (simple code H/W/S/B or FK to tblAddressTypes) iadAddress (FK to addresses) iadStartDate (only if you need to keep address change history) Your data looks like this: (1) 1 H 1 (John lives at address 1) (2) 2 H 1 (Mary lives at address 1) (3) 1 W 2 (John works at address 2) (4) 2 W 3 (Mary works at address 3) The address type field is like the gender field - it's up to you whether you have a reference table or keep it as a simple one-letter code. Whatever you decide, the history tracking dates DO NOT belong in your reference table, but in the junction table. If you want an instructor to have at most ONE address of each type, then make a unique composite key of iadInstructor and iadAddresstype. Just a point on cascading relationships - it often makes sense to have cascading deletes between a main table and a junction table, but it never makes sense to have cascading updates on an autonumber field, because it is impossible to update an autonumber field. Sorry, that was an error on my part, as I know an autonumber field cannot be updated. On this note, I was concerned that since i need to keep track of the history of addresses if selecting cascade updates/deletions would delete the history...I am correct that this is not the case, right? If they change their address that would remain in the InstructorAddress junction table since their is the unique StartDate field? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... 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 realtionship, 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) Sex (or should this be a lookup table? If so, what relationship do I create and where? I know some of you are saying "well an instructor can only be one sex 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 ProvinceStateCounty 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 relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses Now, I obviously need an addressType Table (Home, Work, Shipping,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 shipping and billing) One AddressType has many Addresses (i.e the shipping 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 relationship, or am I wrong that AddressType has many addresses? Am I modeling this correctly as a many to many relationship? Billiam |
|
Thread Tools | |
Display Modes | |
|
|