What is the PK on Junction Table?

[doubledee]

I have a many-to-many relationship like this...

 

member -||------0<- answer ->0-------||- question

 

 

In the "answer" table I have these fields...

- id
- member_id
- question_id
- response

 

 

Should I make "id" my Primary Key or should "member_id" and "question_id" together be the Primary Key??

 

Why??

 

 

Debbie

 

[Muddy_Funster]

Your primary key should be a unique field identifier for that table, so id in this example.  You can make a compound unique index of the member_id and question_id, using both as forign keys, as well as your PK, but PK's should be table independant and not require a relationship to other tables for their uniqueness.  This allows for easier expansion and re-use of key values within the other tables without breaking the cross reference table.

[AyKay47]

funster's reply sums it up nicely.

But to add, when thinking about what field(s) to make a PK, ask yourself what fields must be unique in the table.

typically the auto_incrementing field would be the PK because it must be unique, but as funster stated, you can also use compound PK's if a group of fields must be unique.

 

e.g. if there can be multiples of the member_id and question_id fields separately, but there can only be one unique paring of the two fields, assign the pair a PK.

[Adam]

funster's reply sums it up nicely.

But to add, when thinking about what field(s) to make a PK, ask yourself what fields must be unique in the table.

typically the auto_incrementing field would be the PK because it must be unique, but as funster stated, you can also use compound PK's if a group of fields must be unique.

 

e.g. if there can be multiples of the member_id and question_id fields separately, but there can only be one unique paring of the two fields, assign the pair a PK.

 

No, you would them a unique key. You can only have one primary key.

[AyKay47]

funster's reply sums it up nicely.

But to add, when thinking about what field(s) to make a PK, ask yourself what fields must be unique in the table.

typically the auto_incrementing field would be the PK because it must be unique, but as funster stated, you can also use compound PK's if a group of fields must be unique.

 

e.g. if there can be multiples of the member_id and question_id fields separately, but there can only be one unique paring of the two fields, assign the pair a PK.

 

no, I meant primary key, you can combine fields in a primary key definition.

 

No, you would them a unique key. You can only have one primary key.

[AyKay47]

funster's reply sums it up nicely.

But to add, when thinking about what field(s) to make a PK, ask yourself what fields must be unique in the table.

typically the auto_incrementing field would be the PK because it must be unique, but as funster stated, you can also use compound PK's if a group of fields must be unique.

 

e.g. if there can be multiples of the member_id and question_id fields separately, but there can only be one unique paring of the two fields, assign the pair a PK.

 

no, I meant primary key, you can combine fields in a primary key definition.

 

No, you would them a unique key. You can only have one primary key.

 

Edit: yeah I see what you are saying now Adam, since `id` is already the PK, yeah you would have to make any others unique keys.

[cpd]

Your primary key can be a composite key comprised of two foreign keys, there's nothing wrong with that at all as the primary key should evaluate to the two foreign keys concatenated.

 

In some instances, this is not practical as someone may be able to answer the same question more than once, e.g. they can retake the test. However, in the situation they are only allowed to the test once, the member_id and question_id can create a composite key and be used as the primary key.

 

My advice is evaluate the requirements and see what's best. Another field just for a primary key is not always necessary if two other fields can be put together to make a composite key.

[doubledee]

Your primary key can be a composite key comprised of two foreign keys, there's nothing wrong with that at all as the primary key should evaluate to the two foreign keys concatenated.

 

In some instances, this is not practical as someone may be able to answer the same question more than once, e.g. they can retake the test. However, in the situation they are only allowed to the test once, the member_id and question_id can create a composite key and be used as the primary key.

 

My advice is evaluate the requirements and see what's best. Another field just for a primary key is not always necessary if two other fields can be put together to make a composite key.

 

I'm getting really confused on this because I have so many people telling me to go both directions?!

 

I originally had the two FK's joined as a composite PK.

 

But now I added an auto_increment "id" and made it the PK and instead made my two FK's a composite Unique Key.

 

A User can only answer a question once, and I'm not keeping a history.

 

Some people think every table needs an auto_increment "id" field...

 

I was probably better off just having two FK's as the PK, but who knows?!

 

I am curious about how hard it would be to change from one approach to the other if things changed down the road...

 

If I choose one approach over the other (i.e. id PK vs two FK/PK), what are the consequences?

 

Thanks,

 

 

Debbie

 

[cpd]

If someone makes a statement along the lines of "you need a column called ID in every table which auto increments" they are wrong. You do not need an ID column for every table.

 

The definition of primary key is a single or set of columns which uniquely identifies a row within a table. For example, the PK for a system which maintains "Courses" can be "CourseNo" with values such as "B745A", "B746A", "B182S" etc. There is no requirement to have an auto incrementing integer as your primary key.

 

Moreover, you can have FK's which make up PK's. That again has nothing wrong with it. A good example of this is a library database. You have a table containing the books which includes the author's and genre's. To properly normalise your database you require a many-to-many relationship between the authors and books as well as the genre's and books as each book can have more than one, of each.

 

The "resolution table" can consist of two fields, "bookID" and "authorID" which together form a composite key and also, the primary key. This means the table has only two fields, both of which make up the PK, FK's and together a composite key.

[doubledee]

If someone makes a statement along the lines of "you need a column called ID in every table which auto increments" they are wrong. You do not need an ID column for every table.

 

The definition of primary key is a single or set of columns which uniquely identifies a row within a table. For example, the PK for a system which maintains "Courses" can be "CourseNo" with values such as "B745A", "B746A", "B182S" etc. There is no requirement to have an auto incrementing integer as your primary key.

 

Moreover, you can have FK's which make up PK's. That again has nothing wrong with it. A good example of this is a library database. You have a table containing the books which includes the author's and genre's. To properly normalise your database you require a many-to-many relationship between the authors and books as well as the genre's and books as each book can have more than one, of each.

 

The "resolution table" can consist of two fields, "bookID" and "authorID" which together form a composite key and also, the primary key. This means the table has only two fields, both of which make up the PK, FK's and together a composite key.

 

When would I want a composite Unique Index and an "id" field that is a PK?

 

What would happen if I dropped my auto_increment "id" PK field and then later needed a way to uniquely identify a record?  How would I retroactively fix things?

 

That is one benefit I see with having an "id" field...  You always have a unique auto_increment number to identify every single record, and it is much more human-readable.

 

 

Debbie

 

[cpd]

In your situation, if you were going to have a single user entering an answer for the same question more than once thus creating two rows with the same member_id and question_id, you would not be able to use them as the PK, creating a composite key from them wont really achieve anything either.

 

However, your situation is that a single user will only be able to answer a single question once. In this situation you will only ever have a single row with a member_id and question_id. Therefore, they can be combined to create a composite key and this composite key used as the primary key.

 

I can explain further via voice on skype if you like. Drop me a PM if you'd prefer that as it can be difficult to get across in text.

[doubledee]

In your situation, if you were going to have a single user entering an answer for the same question more than once thus creating two rows with the same member_id and question_id, you would not be able to use them as the PK, creating a composite key from them wont really achieve anything either.

 

However, your situation is that a single user will only be able to answer a single question once. In this situation you will only ever have a single row with a member_id and question_id. Therefore, they can be combined to create a composite key and this composite key used as the primary key.

 

I can explain further via voice on skype if you like. Drop me a PM if you'd prefer that as it can be difficult to get across in text.

 

Okay, so unless I know that I would need an auto_increment later on, you are saying just leverage the two FK's to make my PK.

 

Guess I'll live on the wild side and drop my id, auto_increment, pk.

 

 

Debbie

 

[cpd]

It's entirely up to you. That's how I would do it and that's how many people who understand normalisation would do it, or so I'd hope.

 

That said, please don't think its wrong to create an `id` column. Its only wrong to think this is required in every single table and some people use it as a "quick method" of creating and linking tables; I would argue that's bad practice though.

[doubledee]

It's entirely up to you. That's how I would do it and that's how many people who understand normalisation would do it, or so I'd hope.

 

That said, please don't think its wrong to create an `id` column. Its only wrong to think this is required in every single table and some people use it as a "quick method" of creating and linking tables; I would argue that's bad practice though.

 

Okay, thanks!

 

 

Debbie

 

[Muddy_Funster]

Your primary key can be a composite key comprised of two foreign keys, there's nothing wrong with that at all as the primary key should evaluate to the two foreign keys concatenated.

...

Yeah there is and I already said why.  What to you is the difference between a primary key and a unique index? 

 

How much fun would it be if by accident/corrupition/design one or both of the joining tables was truncated? How about a member leaves and a new one gets assugned the original record ID, then want's to sit the test? A member leaves and you delete the record from the member table, which cascades - either you won't be able to, or worse still, your join table is completly unusable because of duplicate entrys. And all the rest. Primary keys, if used, should never depend on anything outside the table they are applied to.

[kicken]

How much fun would it be if by accident/corrupition/design one or both of the joining tables was truncated?

 

Corruption is something that you'd end up having to restore from a backup from anyway most likely, and is something beyond your control generally.  Having an auto-increment id vs a composite foreign key isn't really going to do much in that case to save you from any headaches.  As for accident/design, then you just did it wrong to start with.  If you design correctly there is nothing wrong with using a composite key.

 

How about a member leaves and a new one gets assugned the original record ID

 

You NEVER re-use an ID.  Once it has been assigned to a member, it is there's forever.  As such, not an issue as every member will have their own id and will never have an issue joining to improper records in the composite table.

 

A member leaves and you delete the record from the member table, which cascades

 

Then those entries in the composite table get deleted as well.  No issue here, that would be desired behavior.  Even if it was not setup to cascade, the worst you would have is that your composite table would end up with useless data because you'd have entries that no longer correspond to a member.  Aside from wasting disk space this is not an issue, and if your concerned about the disk space you can run a periodic maintenance task to clear them out.

 

 

---

 

I use composite primary keys for most of my small glue tables.  If I add it's own separate key it is usually because the number of FK's that would make up the primary is excessive.  For example I have a few tables who's composite PK would consist of 4 different columns from different tables.  It's easier for joining and data-passing purposes to just use a new auto-increment number in those cases.  Also (with mysql innodb at least) the primary key is automatically included as part of every index created so if you have say 4-integer columns at 4-bytes each as your PK that is an automatic 32-bytes added into every index vs a single 4 or 8-byte value for an auto-increment id.

 

[Muddy_Funster]

As for accident/design, then you just did it wrong to start with.

That's a bit of a broad stroke in my opinion.  It could have been done right as to what the specification was at creation.  This is not by default what it will be three months or a year down the line.

Then those entries in the composite table get deleted as well.  No issue here, that would be desired behavior.

Really? because when I tried it one time it only threw forign key constraint violations which I assumed was due to the cascade only deleting the values used to provide part of the unique key index and not all, thus atempting to force what would be duplicate records.

You NEVER re-use an ID.  Once it has been assigned to a member, it is there's forever.  As such, not an issue as every member will have their own id and will never have an issue joining to improper records in the composite table.

Love the ideal world thing you got going on there Unfortunately sometimes the only feasable scenario, especialy when trying to integrate with legacy systems which already do just that, is to build a key indipendantly into the table.

 

I suppose I'm just bias - I spend most of my time in databases trying to plug into structures that have been badly designed, to facilitate visability and reporting on data for new systems. So I instinctivly distrust building keys in that way, preffering to opt for something I can controll directly.  Looking at it from a lees subjective point of view, what you say is perfect sense in relation to a ground up build.