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
|
|||
|
|||
How to implement many-to-many? Do I need primary key defined?
If I have table student and table classes
(1) What is your favorite naming convention to creating the many-to-many table that holds the foreign keys for students and the foreign keys for classes? (2) Should the primary key in my many-to-many relation contain the foreign key of (a) the student or (b) the class or (c) neither? (3) I tried to make the foreign key for student the primary key but since a student might have multiple clases, that primary key might have duplicates and that caused problems. Do I need to have a primary key? Cannot I just have two fields that are indexed? MSAccess complains when I dont' have a primary key. Also: does any one have an example of using a language like VB.NET to programmatically create an MSAccess database? Which would be easier, ADOX or DAO? Thanks, Siegfried |
#2
|
|||
|
|||
1) If the table is simply a construct to resolve the M:M, I'd call it
lnkStudentClass. However, some linking table are entities in and of themselves. In that case, I create a real-world name for it. 2) Usually, I have the Primary Key a compound one created by both foreign keys. The exception is when this linking table has a relationship with another table. Then I will give it an Autonumber Primary Key and create a Unique Index on both the foreign keys. But I know developers who do the later all the time on the principle that it will work in all circumstances. I don't have a problem with this. 3) You can't make the PK of one table the PK of the linking table because there is a One-to-Many relationship between them. Do one of the two things in #2 above. Yes, every table should have a PK. It's not required in Access (it should be) but it's good design practice. Also: Personally I think DAO is easier, faster, and more flexible than ADO. It was created for Access and MS thinks so too. In Access 2003, DAO is now a default Reference. -- --Roger Carlson Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Siegfried Heintze" wrote in message ... If I have table student and table classes (1) What is your favorite naming convention to creating the many-to-many table that holds the foreign keys for students and the foreign keys for classes? (2) Should the primary key in my many-to-many relation contain the foreign key of (a) the student or (b) the class or (c) neither? (3) I tried to make the foreign key for student the primary key but since a student might have multiple clases, that primary key might have duplicates and that caused problems. Do I need to have a primary key? Cannot I just have two fields that are indexed? MSAccess complains when I dont' have a primary key. Also: does any one have an example of using a language like VB.NET to programmatically create an MSAccess database? Which would be easier, ADOX or DAO? Thanks, Siegfried |
#3
|
|||
|
|||
"Siegfried Heintze" wrote in
: (1) What is your favorite naming convention to creating the many-to-many table that holds the foreign keys for students and the foreign keys for classes? I have a horror of one-size-fits-all naming mechanisms. Remember that "junction tables" are just tables and are modelling some real life entity, and that is what should dominate the naming. For example, if the table is modelling what students turned up for which classes, then I would call it TurnedUpFor. If it's about registrations, I would call it Registrations. Alternatives would be BannedFrom, CaughtSmokingIn, QualifiedIn and so on. For me, names like StudentClasses are anathema because they don't tell anyone what they mean. And what happens if you need two relationships (RegisteredFor, AutomaticPassIn) -- how helpful is StudentClasses and ClassesStudents? (2) Should the primary key in my many-to-many relation contain the foreign key of (a) the student or (b) the class or (c) neither? I am a purist: I would make the PK out of the two FKs and nothing else. (3) I tried to make the foreign key for student the primary key but since a student might have multiple clases, that primary key might have duplicates and that caused problems. That's why it's a many-to-many relationship... Do I need to have a primary key? Rule one: if there is no PK then it's not a R table. Cannot I just have two fields that are indexed? MSAccess complains when I dont' have a primary key. In the UI, you can ctrl-click the two fields and then click the primary key toolbar button: this creates a compound key using both fields. Also: does any one have an example of using a language like VB.NET to programmatically create an MSAccess database? Which would be easier, ADOX or DAO? SQL. CREATE TABLE Registrations ( StudentID INTEGER NOT NULL REFERENCES Students, ClassCode NCHAR(5) NOT NULL REFERENCES Classes, DatePaid SMALLDATETIME NULL // etc etc CONSTRAINT PK_Registrations PRIMARY KEY (StudentID, ClassCode) ) You can execute this using anything like ADO, DAO, ADO.NET that you have handy. Hope that helps Tim F |
#4
|
|||
|
|||
(2) Should the primary key in my many-to-many relation contain the
foreign key of (a) the student or (b) the class or (c) neither? I am a purist: I would make the PK out of the two FKs and nothing else. Why (or when) would this be advantagous? To benefit from this I would always have to know both foriegn keys. The whole reason for having the table is that I only know one or the other. Also: does any one have an example of using a language like VB.NET to programmatically create an MSAccess database? Which would be easier, ADOX or DAO? I need to create a database, not just a database table. |
#5
|
|||
|
|||
"Siegfried Heintze" wrote in
: I am a purist: I would make the PK out of the two FKs and nothing else. Why (or when) would this be advantagous? To benefit from this I would always have to know both foriegn keys. The whole reason for having the table is that I only know one or the other. I think we must be at cross-purposes here. I was talking about a "junction table" recording, for example, registrations of students to classes. You may well have times when you have a registration form a particular student who has forgotten to fill in the box for which class he wants to join: but I'd suggest that this would not be a particularly useful thing to record in the database. The Only Purpose of this table is to create links between students and classes: it doesn't make sense to do that when you only know one or the other. Also: does any one have an example of using a language like VB.NET to programmatically create an MSAccess database? Which would be easier, ADOX or DAO? I need to create a database, not just a database table. Oh, okay. Hope that helps Tim F |
#6
|
|||
|
|||
I think we must be at cross-purposes here. I was talking about a "junction table" recording, for example, registrations of students to classes. Tim (and anyone else): I really want to understand this. Please correct any false assumptions because I don't believe we are at cross purposes here. (1) I assume a junction table is the name of table used any time you have a M:M relationship and that this is very common. (2) I assume that performing joins is very common. When Access performs a join, (for example) it enumates each student from which it gets the studentID. (I like to be consistent and always store this in a auto increment integer field called "id" -- do you do the same?) Then, it uses this value and finds all the rows in the junction table whose fkStudent columns matches this ID. Now can someone explain to me how having a composit primary key consisting of both fkStudent and fkClasses helps Access quickly find all the matching rows for our join when we only know the fkStudent value? Can someone explain why having a primary key at all is advantageous here? (Assuming primary keys do not allow duplicates?) It seems I must be missing something here! It seems to me that we want to allow duplicates in both the fkStudent and fkClasses columns (assuming this is a pure link table and we are not storing any data like a grade that describes the relationship between the class and the student) and we want both columns to be indexed so we can not only quicly find all the classes a student has, but also quicly find all the students a class has. Now there was a comment earlier that one should always have an autoincrement integer primary key just in case you need to, for example, uniquely identify a row in the link (or junction table -- same thing?) because you have stored some additional data there like a building and room number, or a grade. What is the ramification of having multiple indices in a single relation? Let us say you have three fields: id (primary key), fkStudent (integer, indexed) and fkClass (integer, indexed). Everytime I insert into this, I have to update three index structures! Should this be a disk space concern? Should this be a data structure corruption concern? Should this be execution time concern? Thanks, Siegfried You may well have times when you have a registration form a particular student who has forgotten to fill in the box for which class he wants to join: but I'd suggest that this would not be a particularly useful thing to record in the database. The Only Purpose of this table is to create links between students and classes: it doesn't make sense to do that when you only know one or the other. |
#7
|
|||
|
|||
Having a compound primary key of both foreign keys will only allow one
unique combination for fkStudent and fkClasses on the assumption that one student can be in any particular class only one time. (By class here, I mean a particular instance of a Course.) Because it is a compound primary key, it allows duplicates of a StudentID and of a ClassID, but no duplicates of a combination of the two. When I mentioned earlier that I will add an autonumber primary key sometimes, that is in the case where the linking table has some relationship to another table. For instance, if you wanted to track assignments handed in, the Assignments table would have a relationship to the linking table (lnkStudentClass). Therefore the relationships would look like this: tblStudent lnkStudentClass tblClass ======== =========== ====== StudentID ---- fkStudent |----ClassID others fkClass ------| SCID | tblAssignments | =========== | AssignmentsID |------- fkSC In this case, I would STILL create a Unique Index on the combination of fkStudent and fkClass. The optimizer for the Jet engine uses indexes (primary keys ARE indexes) to optimize queries, so they should be used. Any field which you use to Join tables, sort on, or used frequently as a criteria in a query should have an index. Also, in order to create Relationships (with Referential Integrity ON) you MUST have a primary key on the One side of a One-to-Many relationship. -- --Roger Carlson Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Siegfried Heintze" wrote in message ... I think we must be at cross-purposes here. I was talking about a "junction table" recording, for example, registrations of students to classes. Tim (and anyone else): I really want to understand this. Please correct any false assumptions because I don't believe we are at cross purposes here. (1) I assume a junction table is the name of table used any time you have a M:M relationship and that this is very common. (2) I assume that performing joins is very common. When Access performs a join, (for example) it enumates each student from which it gets the studentID. (I like to be consistent and always store this in a auto increment integer field called "id" -- do you do the same?) Then, it uses this value and finds all the rows in the junction table whose fkStudent columns matches this ID. Now can someone explain to me how having a composit primary key consisting of both fkStudent and fkClasses helps Access quickly find all the matching rows for our join when we only know the fkStudent value? Can someone explain why having a primary key at all is advantageous here? (Assuming primary keys do not allow duplicates?) It seems I must be missing something here! It seems to me that we want to allow duplicates in both the fkStudent and fkClasses columns (assuming this is a pure link table and we are not storing any data like a grade that describes the relationship between the class and the student) and we want both columns to be indexed so we can not only quicly find all the classes a student has, but also quicly find all the students a class has. Now there was a comment earlier that one should always have an autoincrement integer primary key just in case you need to, for example, uniquely identify a row in the link (or junction table -- same thing?) because you have stored some additional data there like a building and room number, or a grade. What is the ramification of having multiple indices in a single relation? Let us say you have three fields: id (primary key), fkStudent (integer, indexed) and fkClass (integer, indexed). Everytime I insert into this, I have to update three index structures! Should this be a disk space concern? Should this be a data structure corruption concern? Should this be execution time concern? Thanks, Siegfried You may well have times when you have a registration form a particular student who has forgotten to fill in the box for which class he wants to join: but I'd suggest that this would not be a particularly useful thing to record in the database. The Only Purpose of this table is to create links between students and classes: it doesn't make sense to do that when you only know one or the other. |
#8
|
|||
|
|||
Having a compound primary key of both foreign keys will only allow one unique combination for fkStudent and fkClasses on the assumption that one student can be in any particular class only one time. Ah hah! Finally I understand. Thanks Roger. Now what about performance? In scenerio "A" we have no primary key. We have indices fkStudent and fkClass and are at risk of having duplicate Student-class assignments. We perform a join to find out all the classes a student has. Jet gets the first Student, gets his ID and finds all the rows in the link table that have that value for fkStudent. This should be fast because we are indexed on fkStudent. No composit keys. In scenerio "B" we take your advice: We have composit primary key consisting of at least fkStudent and fkClass. How fast is the lookup going to be when doing a join and (I assume) Jet is only going to know the value for fkStudent? Is this going to be a linear search because we don't the other values (segments) of the primary key? Thanks for sticking with me on this! Siegfried |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumber using alpanumerics | Katharine Jansen | Database Design | 18 | August 19th, 2005 12:54 PM |
Create Table Primary Key after Make Table Query And Update Table | RNUSZ@OKDPS | Running & Setting Up Queries | 1 | May 3rd, 2005 08:07 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
COMBOBOX - RECORDS IN TABLE | Samora | New Users | 5 | March 3rd, 2005 02:41 PM |
Muliple primary key | Billy K | Database Design | 4 | May 31st, 2004 02:50 PM |