MySQL table design: Primary Foreign Multi-Column Key possible?

The fastest way for the database to work on this is probably PRIMARY KEY ('user_id', 'friend_id') This ensures that they are unique combinations, and at the same time both can be foreign keys. Maybe you want an index on user_id too, so that you can fast look up all friends for one user INDEX ('user_id') There is no need to have a surrogate key, because it creates extra work related to maintaining the table. The combinations are unique anyways, and you already know both the ids in question.

The fastest way for the database to work on this is probably PRIMARY KEY ('user_id', 'friend_id') This ensures that they are unique combinations, and at the same time both can be foreign keys. Maybe you want an index on user_id too, so that you can fast look up all friends for one user. INDEX ('user_id') There is no need to have a surrogate key, because it creates extra work related to maintaining the table.

The combinations are unique anyways, and you already know both the ids in question.

– Psaniko Jan 9 '10 at 17:43 Probably. But doesn't hurt to be specific. – Tor Valamo Jan 9 '10 at 17:44 Perhaps an index on "friend_id" would be more to the point.

The PK dclaration will result in an index on "user_id" and "friend_id" in that order, and that composite index will be used when looking up a specific user_id. But a separate index will be needed for lookups on friend_id. – Walter Mitty Jan 11 '10 at 15:22 That depends on the database engine in question.

– Tor Valamo Jan 11 '10 at 16:17.

Yes, you can indeed create two foreign key columns, this is often how this association is designed. You can also specify that two columns together are unique so (user_id,friend_id) is unique. Edit: An example of this may be: CREAT TABLE friendship ( user_id INT, friend_id INT, FOREIGN KEY (user_id) REFERENCES user(id), FOREIGN KEY (friend_id) REFERENCES user(id), UNIQUE(user_id,friend_id) ).

I'd go for a single-column surrogate key (friendship_id) plus unique constraint and an index on the user_id, friend_id combination. I'd use the surrogate key for consistency, since you seem to be using surrogates (user_id is presumably referring to a column id on user etc. ) on other tables.

If you need to track friendship events, you could have a friendship_id (say you want a box listing the most recent friendships in the system with a link to details), but if your data model doesn't require that relationship, a multi-column primary key would be just fine. You can create it like so: create table friend ( user_id int, friend_id int, foreign key (user_id) references user(id), foreign key (friend_id) references user(id), primary key (user_id, friend_id) ); there will be an index on both columns individually created by the foreign key constraint, and a multi-column unique index on user_id, friend_id.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.