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 |
#21
|
|||
|
|||
Database Design Normalization help
Thanks for your help so far Duane, I really appreciate it !!!!
It seems that while I may understand that a many to many relationship is resolved by using an intermediate table or Junction table, with each entity on the one side of a many relationship to the junction box, my relationship diagram does not reflect that...which leads me to thinking I am not doing something correctly. Can you tell me if this is the correct procedure for the following many to many realtionship? tblInstructors instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) Instructor ID# (starts with zeros so a text field) FirstName Midname Lastname tblAddresses AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) AddressLine1 AddressLine2 AddressLine3 City ProvinceStateCounty PostalCode Country tblInstructorAddresses InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) As you can see from my relationship report, InstructorID in tblInstructors is realted one to many with InstructorID in tblInstructorAddresses. It is my understanding that this is how every many to many realtionship is resolved. You will note, however, that AddressID from tblAddresses realtes to AddressID in tblInstructorAddresses as a one to one---not a one to many as I suspect it should. Why is this happening, Duane? (BTW, I create the realtionship by taking the primary ID from the table and dropping it on top of the foreignkeyiD in the junction/intermediate table.) The really odd thing is that when i click on edit the relationship for Instructor to instructoraddress it says it is a one to one relationship between the instructorID's, however, the diagram clearly shows it as a one to many relationship! Man am I confused! Billiam |
#22
|
|||
|
|||
Database Design Normalization help
Your design:
InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) Your FK should allow duplicates. The fields normally would be Long Integer depending on the data type in the instructor and address tables. You can create a unique index on a combination of the InstructorID and AddressID. -- Duane Hookom Microsoft Access MVP "Billiam" wrote: Thanks for your help so far Duane, I really appreciate it !!!! It seems that while I may understand that a many to many relationship is resolved by using an intermediate table or Junction table, with each entity on the one side of a many relationship to the junction box, my relationship diagram does not reflect that...which leads me to thinking I am not doing something correctly. Can you tell me if this is the correct procedure for the following many to many realtionship? tblInstructors instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) Instructor ID# (starts with zeros so a text field) FirstName Midname Lastname tblAddresses AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) AddressLine1 AddressLine2 AddressLine3 City ProvinceStateCounty PostalCode Country tblInstructorAddresses InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) As you can see from my relationship report, InstructorID in tblInstructors is realted one to many with InstructorID in tblInstructorAddresses. It is my understanding that this is how every many to many realtionship is resolved. You will note, however, that AddressID from tblAddresses realtes to AddressID in tblInstructorAddresses as a one to one---not a one to many as I suspect it should. Why is this happening, Duane? (BTW, I create the realtionship by taking the primary ID from the table and dropping it on top of the foreignkeyiD in the junction/intermediate table.) The really odd thing is that when i click on edit the relationship for Instructor to instructoraddress it says it is a one to one relationship between the instructorID's, however, the diagram clearly shows it as a one to many relationship! Man am I confused! Billiam |
#23
|
|||
|
|||
Database Design Normalization help
Yes, now the relationships are one to many, thanks!
"Duane Hookom" wrote: You can create a unique index on a combination of the InstructorID and AddressID. To clarify,does this mean I do not need the InstructorAddressesID as the Primary key in tblInstructorAddresses? And if so, does this also mean that the InstructorID and AddressID foreign keys combo make up the primary key of tblInstructorAddresses as a composite key? Billiam "Billiam" wrote: Thanks for your help so far Duane, I really appreciate it !!!! It seems that while I may understand that a many to many relationship is resolved by using an intermediate table or Junction table, with each entity on the one side of a many relationship to the junction box, my relationship diagram does not reflect that...which leads me to thinking I am not doing something correctly. Can you tell me if this is the correct procedure for the following many to many realtionship? tblInstructors instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) Instructor ID# (starts with zeros so a text field) FirstName Midname Lastname tblAddresses AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) AddressLine1 AddressLine2 AddressLine3 City ProvinceStateCounty PostalCode Country tblInstructorAddresses InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) As you can see from my relationship report, InstructorID in tblInstructors is realted one to many with InstructorID in tblInstructorAddresses. It is my understanding that this is how every many to many realtionship is resolved. You will note, however, that AddressID from tblAddresses realtes to AddressID in tblInstructorAddresses as a one to one---not a one to many as I suspect it should. Why is this happening, Duane? (BTW, I create the realtionship by taking the primary ID from the table and dropping it on top of the foreignkeyiD in the junction/intermediate table.) The really odd thing is that when i click on edit the relationship for Instructor to instructoraddress it says it is a one to one relationship between the instructorID's, however, the diagram clearly shows it as a one to many relationship! Man am I confused! Billiam |
#24
|
|||
|
|||
Database Design Normalization help
I almost always create a single primary key field in a table which is
generally an autonumber. I do this because I create lots of web pages and I would like to pass the value of a single field to a web page for editing a single record. You could remove the autonumber primary key in a small junction table if you want and replace it with the combination of the two fields. I wouldn't because I am lazy (consistently lazy ;-) -- Duane Hookom Microsoft Access MVP "Billiam" wrote: Yes, now the relationships are one to many, thanks! "Duane Hookom" wrote: You can create a unique index on a combination of the InstructorID and AddressID. To clarify,does this mean I do not need the InstructorAddressesID as the Primary key in tblInstructorAddresses? And if so, does this also mean that the InstructorID and AddressID foreign keys combo make up the primary key of tblInstructorAddresses as a composite key? Billiam "Billiam" wrote: Thanks for your help so far Duane, I really appreciate it !!!! It seems that while I may understand that a many to many relationship is resolved by using an intermediate table or Junction table, with each entity on the one side of a many relationship to the junction box, my relationship diagram does not reflect that...which leads me to thinking I am not doing something correctly. Can you tell me if this is the correct procedure for the following many to many realtionship? tblInstructors instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) Instructor ID# (starts with zeros so a text field) FirstName Midname Lastname tblAddresses AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) AddressLine1 AddressLine2 AddressLine3 City ProvinceStateCounty PostalCode Country tblInstructorAddresses InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) As you can see from my relationship report, InstructorID in tblInstructors is realted one to many with InstructorID in tblInstructorAddresses. It is my understanding that this is how every many to many realtionship is resolved. You will note, however, that AddressID from tblAddresses realtes to AddressID in tblInstructorAddresses as a one to one---not a one to many as I suspect it should. Why is this happening, Duane? (BTW, I create the realtionship by taking the primary ID from the table and dropping it on top of the foreignkeyiD in the junction/intermediate table.) The really odd thing is that when i click on edit the relationship for Instructor to instructoraddress it says it is a one to one relationship between the instructorID's, however, the diagram clearly shows it as a one to many relationship! Man am I confused! Billiam |
#25
|
|||
|
|||
Database Design Normalization help
Sorry, Duane, I am not sure what you meant by "You can create a unique index
on a combination of the InstructorID and AddressID." as stated a few threads back... I am not sure if you were offering another way to index the junction table, or ? Sorry, I am really new to this, and sometimes all the reading I've done just makes you more confused! "Duane Hookom" wrote: I almost always create a single primary key field in a table which is generally an autonumber. I do this because I create lots of web pages and I would like to pass the value of a single field to a web page for editing a single record. You could remove the autonumber primary key in a small junction table if you want and replace it with the combination of the two fields. I wouldn't because I am lazy (consistently lazy ;-) -- Duane Hookom Microsoft Access MVP "Billiam" wrote: Yes, now the relationships are one to many, thanks! "Duane Hookom" wrote: You can create a unique index on a combination of the InstructorID and AddressID. To clarify,does this mean I do not need the InstructorAddressesID as the Primary key in tblInstructorAddresses? And if so, does this also mean that the InstructorID and AddressID foreign keys combo make up the primary key of tblInstructorAddresses as a composite key? Billiam "Billiam" wrote: Thanks for your help so far Duane, I really appreciate it !!!! It seems that while I may understand that a many to many relationship is resolved by using an intermediate table or Junction table, with each entity on the one side of a many relationship to the junction box, my relationship diagram does not reflect that...which leads me to thinking I am not doing something correctly. Can you tell me if this is the correct procedure for the following many to many realtionship? tblInstructors instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) Instructor ID# (starts with zeros so a text field) FirstName Midname Lastname tblAddresses AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) AddressLine1 AddressLine2 AddressLine3 City ProvinceStateCounty PostalCode Country tblInstructorAddresses InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) As you can see from my relationship report, InstructorID in tblInstructors is realted one to many with InstructorID in tblInstructorAddresses. It is my understanding that this is how every many to many realtionship is resolved. You will note, however, that AddressID from tblAddresses realtes to AddressID in tblInstructorAddresses as a one to one---not a one to many as I suspect it should. Why is this happening, Duane? (BTW, I create the realtionship by taking the primary ID from the table and dropping it on top of the foreignkeyiD in the junction/intermediate table.) The really odd thing is that when i click on edit the relationship for Instructor to instructoraddress it says it is a one to one relationship between the instructorID's, however, the diagram clearly shows it as a one to many relationship! Man am I confused! Billiam |
#26
|
|||
|
|||
Database Design Normalization help
When you view the indexes in your table design view, you have the opportunity
to select more than one field and provide a single name for the index. You can set these indexes to not allow duplicates. This is an alternative to the single field primary key. I usually create a single field primary key and a unique index on the other foreign key fields. "Billiam" wrote: Sorry, Duane, I am not sure what you meant by "You can create a unique index on a combination of the InstructorID and AddressID." as stated a few threads back... I am not sure if you were offering another way to index the junction table, or ? Sorry, I am really new to this, and sometimes all the reading I've done just makes you more confused! "Duane Hookom" wrote: I almost always create a single primary key field in a table which is generally an autonumber. I do this because I create lots of web pages and I would like to pass the value of a single field to a web page for editing a single record. You could remove the autonumber primary key in a small junction table if you want and replace it with the combination of the two fields. I wouldn't because I am lazy (consistently lazy ;-) -- Duane Hookom Microsoft Access MVP "Billiam" wrote: Yes, now the relationships are one to many, thanks! "Duane Hookom" wrote: You can create a unique index on a combination of the InstructorID and AddressID. To clarify,does this mean I do not need the InstructorAddressesID as the Primary key in tblInstructorAddresses? And if so, does this also mean that the InstructorID and AddressID foreign keys combo make up the primary key of tblInstructorAddresses as a composite key? Billiam "Billiam" wrote: Thanks for your help so far Duane, I really appreciate it !!!! It seems that while I may understand that a many to many relationship is resolved by using an intermediate table or Junction table, with each entity on the one side of a many relationship to the junction box, my relationship diagram does not reflect that...which leads me to thinking I am not doing something correctly. Can you tell me if this is the correct procedure for the following many to many realtionship? tblInstructors instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) Instructor ID# (starts with zeros so a text field) FirstName Midname Lastname tblAddresses AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) AddressLine1 AddressLine2 AddressLine3 City ProvinceStateCounty PostalCode Country tblInstructorAddresses InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) As you can see from my relationship report, InstructorID in tblInstructors is realted one to many with InstructorID in tblInstructorAddresses. It is my understanding that this is how every many to many realtionship is resolved. You will note, however, that AddressID from tblAddresses realtes to AddressID in tblInstructorAddresses as a one to one---not a one to many as I suspect it should. Why is this happening, Duane? (BTW, I create the realtionship by taking the primary ID from the table and dropping it on top of the foreignkeyiD in the junction/intermediate table.) The really odd thing is that when i click on edit the relationship for Instructor to instructoraddress it says it is a one to one relationship between the instructorID's, however, the diagram clearly shows it as a one to many relationship! Man am I confused! Billiam |
#27
|
|||
|
|||
Database Design Normalization help
I am still confused...I tried Access help and found this:
"An index is a Microsoft Access feature that speeds up searching and sorting in a table." Okay, I get this. "You can create an index for a field in the Indexes window (Indexes window: In an Access database, a window in which you can view or edit a table's indexes or create multiple-field indexes.) Okay, would it have been too much to show a picture of what the window looks like, or how to get to that window??? Do they simply mean when you open your table in design view? (On top you have field name and datatype, on the bottom General and lookup tab?) of table Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), or by setting the field's Indexed property to Yes." So do you mean that each field in a table (or query etc) has the possibility of being indexed? You could use one field and index it. Or you could have more than one field, and therefore indexes. If so, here is where I am confused: When you view the indexes in your table design view (OK at this point) you have the opportunity to select more than one field (okay to this point) and provide a single name for the index. What do you mean by provide a single name for the index---the Table name? Sorry Duane, I hope you are not pulling your hair out, but I really need to undestand this basic stuff correctly, as I am mostly following a recipe right now---I know I need to make a cake, that it has ingredients, they have to get mixed in certain relationships, etc etc...but I still do not have the reasons for baking etc down pat! BTW, if you answer, "Did someone say cake?" I would not blame you ! ;-] Billiam "Duane Hookom" wrote: When you view the indexes in your table design view, you have the opportunity to select more than one field and provide a single name for the index. You can set these indexes to not allow duplicates. This is an alternative to the single field primary key. I usually create a single field primary key and a unique index on the other foreign key fields. "Billiam" wrote: Sorry, Duane, I am not sure what you meant by "You can create a unique index on a combination of the InstructorID and AddressID." as stated a few threads back... I am not sure if you were offering another way to index the junction table, or ? Sorry, I am really new to this, and sometimes all the reading I've done just makes you more confused! "Duane Hookom" wrote: I almost always create a single primary key field in a table which is generally an autonumber. I do this because I create lots of web pages and I would like to pass the value of a single field to a web page for editing a single record. You could remove the autonumber primary key in a small junction table if you want and replace it with the combination of the two fields. I wouldn't because I am lazy (consistently lazy ;-) -- Duane Hookom Microsoft Access MVP "Billiam" wrote: Yes, now the relationships are one to many, thanks! "Duane Hookom" wrote: You can create a unique index on a combination of the InstructorID and AddressID. To clarify,does this mean I do not need the InstructorAddressesID as the Primary key in tblInstructorAddresses? And if so, does this also mean that the InstructorID and AddressID foreign keys combo make up the primary key of tblInstructorAddresses as a composite key? Billiam "Billiam" wrote: Thanks for your help so far Duane, I really appreciate it !!!! It seems that while I may understand that a many to many relationship is resolved by using an intermediate table or Junction table, with each entity on the one side of a many relationship to the junction box, my relationship diagram does not reflect that...which leads me to thinking I am not doing something correctly. Can you tell me if this is the correct procedure for the following many to many realtionship? tblInstructors instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) Instructor ID# (starts with zeros so a text field) FirstName Midname Lastname tblAddresses AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) AddressLine1 AddressLine2 AddressLine3 City ProvinceStateCounty PostalCode Country tblInstructorAddresses InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref. integrity enforced) InstructorID (FK, Index Yes No duplicates, JOIN Type 1) AddressID (FK, Index Yes No Duplicates JOIN Type 1) As you can see from my relationship report, InstructorID in tblInstructors is realted one to many with InstructorID in tblInstructorAddresses. It is my understanding that this is how every many to many realtionship is resolved. You will note, however, that AddressID from tblAddresses realtes to AddressID in tblInstructorAddresses as a one to one---not a one to many as I suspect it should. Why is this happening, Duane? (BTW, I create the realtionship by taking the primary ID from the table and dropping it on top of the foreignkeyiD in the junction/intermediate table.) The really odd thing is that when i click on edit the relationship for Instructor to instructoraddress it says it is a one to one relationship between the instructorID's, however, the diagram clearly shows it as a one to many relationship! Man am I confused! Billiam |
Thread Tools | |
Display Modes | |
|
|