index key for joining table 1

[RobBroekhuis]

A rather basic question from a relational database newbie:
Say I have three tables:

Members (key: m_id)
Groups (key: g_id)
Membership

Membership only exists to establish a many-to-many relationship between Members and Groups (each member may be part of several groups). The only fields I really need in Membership are foreign keys m_id and g_id. According to books I've read, I should have a primary key in the membership table, but I don't see the point. Even if I had a key, say ms_id, I would never know it, would always have to query for it. The table is almost always used in join queries (only time this is not the case is when establishing or removing membership).
Can someone explain why it's beneficial to have a key in this situation?


Rob

 

[sleipnir214]

Foreign keys are not indexes. They are constraints, which limit the input to one field based on the contents of another field in another table.

Indexs, of which a primary key is a special type, are used to speed queries.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[RobBroekhuis]

Yes - understood. And in fact, I would (this is just an example case, not my real one) index Membership on both foreign keys, to speed along queries to get either at all of the members of a group, or at all of the groups to which a member belongs. My question is - is there any value in having a PRIMARY KEY on the Membership table. I can't think of any case in which it's helpful (won't help speed queries, won't help maintain data integrity), yet the books suggests I should have one.


Rob

 

[sleipnir214]

All a primary key is is a not-null, unique index. However, when MySQL is deciding which indeces to use in a query, the primary key will take precedence over other indeces. By choosing the right field for your primary key, you can speed queries.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[BNPMike]

A primary key is a logical thing. It relates to how you uniquely identify a record. You should have at least one key (for it to be a relation) but sometimes there are multiple keys, in which case you usually choose the shortest one as the primary key.

The idea about databases in general is first model the logical content and then move later to worry about effiency/speed etc.

In your case Member clearly has a key and in this case there is no dispute it is a primary key.

Some relational databases put indexes on primary keys but that's an issue of tuning as far as you're concerned. A reason they do it is it makes it easy for them to enforce the key constraint ie no duplicates. If you were MySQL and each time you were asked to insert a record into a 3 million record table, you'd have a real job checking the key value was not already present. Unless you had an index...

 

[RobBroekhuis]

No argument with any of what you're saying. Certainly, the Members and Groups tables need keys, and should be indexed on these keys. My question pertains to the linking table, Membership. Each record in this table establishes the membership of one member in one group. The Membership table therefore clearly needs the two keys from the other tables as fields, and would best be indexed on both of those fields. My question is: is it sufficient for the table to have JUST those fields? Neither field is, by the very nature of the relationship, unique, although the combination of the two fields is. Following the direction of books I've read, I could add a third field, a unique key, to this linking table. Now, each record would have its own unique key. But I can't for the life of me figure out how that helps, either from the standpoint of data integrity or database efficiency. Am I reading the wrong books, or am I just not getting it?


Rob

 

[sleipnir214]

I don't think that you would gain any performance benefit from creating a third column just for the purposes of hanging a primary key.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[RobBroekhuis]

Good. So I'm not crazy after all
Thanks for taking the time to help me think this through.


Rob

 

[sleipnir214]

One addendum....

I usually use an auto_increment primary key on tables, and I use those columns to relate records. This gives me the ability to change any part of a record which pertains to an object recorded without having to worry about distubing my ability to continue to relate records.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[r937]

Rob, the books you have been reading should have made it quite clear that the primary key of the Membership table should be the composite of the two linking keys

a linking table like this should not have an auto_increment primary key

fact 1. the declaration of a primary key always results in the creation of a unique index which is very useful for the efficiency of queries

fact 2. the purpose of a primary key is so that you can have other tables reference it in foreign keys

therefore, if the linking table (Membership in this example) will never have a child table, then it does not need a primary key

tables which do not need a primary key may indeed need an index, but that does not mean they must have a primary key

it's all academic in mysql anyway, since foreign keys don't work

and even if the linking table does have a child table, i would strongly caution you not to use an auto_increment primary key



rudy

http://r937.com/

SQL Consulting

 

[RobBroekhuis]

Rudy,
Thanks for the additional wisdom. I guess the books I've read have been more how-to, a little short on database theory...
I understand all of your points, with the exception of the last one: if my linking table were to have a child table, how would I go about not using an auto_increment primary key? Would I use both of the foreign keys as fields again in the child table?

Rob

 

[r937]

yes

Members
m_id PK

Groups
g_id PK

Membership
m_id PK FK
g_id PK FK

MembershipChild
m_id PK FK
g_id PK FK
c_id PK

this has the added advantage that the relationships are "transitive" and you can use the MembershipChild FK columns (which actually link to the Membership PK) to perform joins directly to Groups or Members without joining to Membership

if you use a surrogate key for the PK of Membership, then you would have

Membership
s_id PK
m_id FK
g_id FK

MembershipChild
s_id PK FK
c_id PK

and then if you wanted, say, to join Groups to MembershipChild, you would always have to join through Membership first



rudy

http://r937.com/

SQL Consulting

 

[BNPMike]

r937

A key is something every table should have to be relational. None of the relational dbmses I've come across enforce that but it is a bad and pointless design to have tables without keys.
The term 'primary' is just to distinguish alternative keys where they exist.

A key enforces uniqueness ie rejects duplicates. The fact that it often participates in relationships is incidental.

 

[RobBroekhuis]

Thanks to both of you for your detailed answers! I think I understand now ;-)


Rob