If I have a Junction Table, do I need an additional "ID" field at the top to serve as my Primary Key, or can I just use the two fields that point back to the Parent Tables in the many-to-many?

For example...



Sincerely,


Rob

I always include a recordid because I expect that at some stage in the development of an app I will need to refer to the specific record .

Just to be sure - there is a functional difference whether you add a surrogate key and use it as the primary key or whether you use the pair of foreign key as the composite primary key.

When you opt to use composite primary key consisting of the pair of foreign key, the table will not allow duplicate entries. That may or may not be desirable, depending on what the junction table does.

For a junction table representing a "selection" between an entity and several possible values, it seldom makes sense to have duplicate "selection". OTOH, if the junction table represent something more of a transaction (think of a payment table where you have a customerID and an invoice where the customer makes multiple payment on the same invoice on a regular intervals), then duplicate of the pair of foreign keys is desirable.

Note furthermore that you still can choose to add a individual surrogate key to the table AND create an unique index composing the pair of the foreign keys which gives you the same effect of restricting the entries to only one instance of any possible combination between two foreign key.

IME, for the "selection" junction tables, an ID field may be extraneous because you're typically referring to one of the foreign key and wanting to select all possible values from the other table or something similar. With a "transaction" junction table, there can be a reason for wanting a single record, so having a single column primary key simplifies things.

YMMV.

Hi Rob,

Strictly speaking, no a junction table does not need an id. As BR suggests, you can use a surrogate key to enforce uniqueness on the pairing but that is a somewhat different issue from the question you have raised.

Along with Peter and others, I like every table to have an id field. While you may not initially use the field except in the junction table, if you need at some time to refer to the junction record, having the id field will simplify the reference. Otherwise the referring table will require a composite foreign key to create the join.

The cost of having the id field in performance and space is minimal, so why not include it in your design, 'just in case.'

Glenn

I believe that would be only true if you did want to select one particular record. For a junction tables that function as "selection" of some kind, it's more often that we're pulling in all records based on one of the pair foreign keys, not both. Thus, joining on only one foreign key works well enough for this scenario. I honestly can't recall a time when I needed a particular record of a "selection" junction table but that may be just a happy accident.

It looks like my experience is different as just the reverse seems to be the norm for me, BR. I frequently need to refer to individual records.

Ultimately, I'd say that whether to include a junction table id or not, comes down to knowing the data and how it will be used. I err on the side of having one in case it might be needed as opposed to not having one and then needing to create the field later in the course of development.

Glenn

I've seen this as well. I tend to select a single record more than a subset of them.

A larger factor for me though is whether I think I'll need to include this table in a relationship further down the line. If I do, I include an ID field, if not I don't worry about it (I don't want to have to run composite keys down a line of relationships, that's pretty ugly to me, but merely a matter of preference).

That said, I think 99% of the junction tables I've ever used have had an ID field. I agree with Glenn as well in the fact that it's much easier to put one in at the start than it is to realize later on down the line that you wish you had.

Cheers,

Cool, thanks for sharing, Glenn & Jack.

I don't argue the point that it's easier to have ID there from start and tend to think that an unique index constraining the foreign keys is preferable because as Jack said, if the junction table later becomes a one-side table of a new one-many table, I sure don't want the composite primary key complicating the query.

In cases where this happened, though, I simply added the needed ID and none of my existing queries needs to be modified because the intention is still essentially correct for their job; pull in all "selections" for a given key of a data table. With "transaction" junction table, that is different thing, and I almost always have ID there.

BananaRepublic,

My "answer" junction table links the tables "member" and "question".

The are many Members, and many Questions, but one Member will only answer one Question once. (If you change your Answer to a Question, then we are overwriting things.

So based on what you said, I could create a composite PK from the two FK's in my junction table.

As far as having an ID, I am actually doing this in MySQL. I know you can only have one PK, and I'll have to look if I can first make a composite PK out of "member_id" and "question_id" in the "answer" table and then add a new field called "id" and maybe just put a "unique index" on it. (I'm just not sure if I can use the "auto number" feature if I have a "composite PK" if you follow me?!

Sincerely,


Rob

In MySQL, that'd be expressed as:

A single PK with composite unique index


A composite PK:


NOTE: Foreign key constraint do not work with MyISAM; you may also need to specify they're to be used by engine=InnoDB if foreign key constraint is important to you. Otherwise, the script will execute with no errors but there wont' be actual constraints.

Currently "member_id" and "question_id" are my FK's in "answer" and they form a composite PK.

I just added "id" and tried giving it "unsigned", "auto_increment", "unique_key" and it failed.





Rob

This post has been edited by robert_trace: Apr 2 2012, 12:38 PM

IIRC, auto_increment must be a primary key. Consult MySQL manual on the auto_increment requirement.

So here is my second attempt...

I dropped the Primary Key index. I then added an "id" field as such "mediumint(8)", "unsigned", "auto_increment", "primary_key"

Next I took "member_id" and "question_id" and made a new index called "answer_u_idx" that is a "unique_index" on those two fields, thus making them a "pseudo composite PK", right?

Now, based on everyone's comments, "Is that what is in my best interest right now based on what might happen in the future?"

What happens 10,000 records later if I decide that having a PK named "id" is pointless, and I want my PK to be a composite of my two FK's?

What happens if I left things as they were with no PK named "id" and 10,000 records later I decided I did need that?

Again the context of this is...

Members have User Profiles, and in each Profile is - currently - a series of 10 open-ended questions that people can choose to answer if they so desire. Questions are trying to get people to talk more about themselves and their views. For example, "Why did you get involved in the Boy Scouts?" or "What can be done to expand Scouting in a world full of video games?"

I will NOT be keeping a history of Answers. If you answer one of the samples above, and then later change your mind and answer it differently, then your original Answer gets erased!

Could another table come off of Answers? Maybe, but not likely.

I can leave my new "id" PK in my table as long as having a Unique Index on two Fields serves the core function that I need which is to ensure that for any given Question, there can only ever be one Answer for a given Member.

Whew!

I'll let all of yo experts talk now! (IMG:style_emoticons/default/wink.gif)

Sincerely,


Rob