Re: Many to many

In article <3a2bffba.52190770_at_news1.telia.com>, Arne Jonsson <arne.b.jonsson_at_telia.com> writes
>For about two years I´ve been living happily in the belief that if you
>have a many to many-relation you split it in a third table between the
>other two, where you make the primary keys of these two tables to a
>composite primary key in the third.

This is true. But when you create the new table you should take a close look at it and see what real-world entity it corresponds to. You may find that there are other fields that should also be in this table.

> Now, taking a course in
>SQL-server, I am taught that the two primary keys can be used as two
>foreign keys in the new table, or as one primary and one foreign key.

The two fields are always foreign keys. That's their function in the link-table.

You shouldn't ever see a situation where just one of the fields is a primary key in the link-table. If that happened it would mean that there is a 1:1 relationship between one of the parent tables and the link-table.

It's physically possible to implement that in a relational database, but I can't imagine a situation where I might want to. (But I'm sure someone will be along soon with an example.)

>This have made me confused.

I can understand that.

-- 
Bernard Peek
Not necessarily speaking for: POSTer Audience Research -- POSTAR Ltd.
27 Sale Place, London, W2 1YR. (020 7479-9700)
Bernard_at_postar.co.uk