CODEWITHSUNDEEP
database-designentity-relationship
Kerry Macleod
Kerry Macleod

Reputation: 103

Duplicate primary keys in many-to-many relationship

There is a m:n relationship between the two tables tArticle and tCustomer. Whenever a customer buys an item, a link between the article and the customer is stored in the third table with an additional attribute containing which amount the customer bought.

tArticle:

kArticle | title | stock
---------+-------+------
1        | Water | 39
2        | Apple | 14

tCustomer:

kCustomer | surname | firstName
----------+---------+----------
1         | Muller  | Max
2         | Meier   | Tom

tCustomer_tArticle:

kCustomer | kArticle | number
----------+----------+---------
1         | 2        | 2
2         | 2        | 5
2         | 2        | 3

I know it's highly recommended that you use the combined foreign keys as primary key. But because the same customer can buy the same article twice this wouldn't be unique anymore.

Now my question is do I need to add an AUTO_INCREMENT primary key to the third table or should I use the physical order to have unique records.

My second question is if there is a way to specify in an entity relationship diagram if a link in a m:n relation may appear twice.

Upvotes: 2

Views: 1442

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I don't know where it is "highly recommended" that the two foreign keys be the primary key. That is definitely one approach under the right circumstances. I prefer the approach of always assigning a unique auto-incremented key to a table, unless space overhead is a big issue. Auto-incremented keys provide:

  • Unique references to each row for updates and deletes.
  • A record of the insertion order.
  • The possibility of declaring simpler foreign keys to the table.

In essence, your table is a transaction table. I would recommend not only an auto-incremented key but also a transaction date/time and perhaps other information.

As for this comment:

or should I use the physical order to have unique records.

SQL tables represent unordered sets. There is no physical order that you should see a useful for accessing rows. If you care about insertion order, then you really do need an auto-incremented key.

Upvotes: 2

nvogel
nvogel

Reputation: 25534

Consider what information you are trying to represent in the tCustomer_tArticle table. In the following case for example:

kCustomer | kArticle | number
----------+----------+---------
2         | 2        | 3
2         | 2        | 3

Does that just mean that Tom bought six apples or does it mean there were two transactions each of three apples? If the table is just supposed to be a record of the quantity then you can make kCustomer, kArticle the key and record the same information with one row:

kCustomer | kArticle | number
----------+----------+---------
2         | 2        | 6

If the table is supposed to record individual transactions then it seems there is information missing from the table. Given your original example with three rows of sample data, possibly it's unclear whether that is one, two or three transactions. I would expect there to be some kind of transaction identifier as part of a composite key. I don't think an auto-incrementing number will help. You need to determine the meaningful key that makes sense of the business domain first before you think about adding surrogate keys.

Upvotes: 1

LomoY
LomoY

Reputation: 51

"Now my question is do I need to add an AUTO_INCREMENT primary key to the third table or should I use the physical order to have unique records."

What do you mean by "the physical order to have unique records"?

In your case, I will suggest adding an AUTO_INCREMENT PK, say:

order_id PRIMARY KEY AUTO_INCREMENT

My reason is: Think about when you go to McDonald's, you will get a receipt with an order id on it. when you purchase food again, you will get a different order id on a new receipt. So, the attribute order_id makes each order unique.

"if there is a way to specify in an entity relationship diagram if a link in a m:n relation may appear twice"

As far as I know, nope. I assume you wanna your E-R model reflects how many times (or a customer could purchase the same item more than one times), if so, you can think this question in the following way:

Could tCustomer_tArticle(order_id, kCustomer, kArticle, number) reflect this feature? Yes. we can do:

SELECT order_id,kCustomer,kArticle,number FROM tCustomer_tArticle WHERE kCustomer="Tom";

This will give us the result of how many orders Tom made.

As long as you have a PK to indicate each order, you get the answer of your second question.

Upvotes: 1

Related Questions