Composite primary keys vs auto increment primary key in sql workbench

Question

I need the advice of someone who has a greeter experience.

I have an associative entity in my database, like that:

Table2-> CustomerID, ServiceID, DateSub

Since the same customer (with PK, for example 1111) can require the same service (with PK, for example 3) more than once but never in the same date , the composite PK of Table 2 can't be just (CustomerID, ServiceID).

Now I have 2 options:

1- Also "DateSub" will be a primary key, so the PK of table 2 will be (CustomerID, ServiceID, DateSub)

2- Create a specific PK for the associative entity (for example, Table2ID, and so CustomerID and Service ID will be FK)

Which of the 2 approach would you follow and why? Thank you

2nd options is always better!. Cuz date should never be Primary Key. Ton of customers do the same service on the same day. So you need a specific PK for your table like 2nd option like you mentioned above ``Table2_id`` or just ``Id`` with Auto Increament checked — Nguyễn Văn Quyền, Apr 08 '21 at 09:28
A primary key is a unique key so your second option would allow only 1 per cutomer irrespective of service..and do you anticipate that only one service request per day per customer. — P.Salmon, Apr 08 '21 at 09:31
I think that synthetic primary key is the best option - if it is needed at all. The uniqueness of `(CustomerID, ServiceID, DateI)` expression may be controlled by additional UNIQUE index if needed (but I doubt that it is useful). — Akina, Apr 08 '21 at 09:40
PS. If you'll use composite PK nevertheless then think carefully for the most useful columns order in the index expression (it must improve the most part of queries to this table). Do not forget that PK is clustered index. — Akina, Apr 08 '21 at 09:45
@P.Salmon Yes I specified that there can be just one per day, so adding also Date as PK would guarantee the uniqueness of the combo (Customer ID - Service ID - Date ) — Jenny, Apr 08 '21 at 10:36

score 1 · Answer 1 · answered Apr 08 '21 at 09:42

1

First of all you need to decide whether is it your requirement to make combination of CustomerID, ServiceID amd DateI column as unique? If so then you should go for firt option.

Otherwise I would go for second option.

With first option if DateI is of date data type you will not be able to insert same service for a customer twice. If it's datetime then it's doable though.

If you want to use this primary key (composite primary key) in any other table as foreign key then you need to use all three columns there too.

answered Apr 08 '21 at 09:42

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

I now added a clarification: The same customer can require the same service, but not more than once in the same date ( The service lasts one-3 months ) – Jenny Apr 08 '21 at 09:46
Is there any possibility to use this primary key as foreign key in any other table? – Kazi Mohammad Ali Nur Romel Apr 08 '21 at 09:52
No, there's not – Jenny Apr 08 '21 at 10:00
Then I would like to go to the first point. If uniqueness in those columns are not necessary then I would go for second option. – Kazi Mohammad Ali Nur Romel Apr 08 '21 at 10:04
You can read the answer of Gordon Linoff in below link. https://stackoverflow.com/questions/50043071/pros-cons-of-date-column-as-part-of-primary-key#:~:text=The%20primary%20key%20is%20composite,ID%20on%20the%20Parts%20Table. – Kazi Mohammad Ali Nur Romel Apr 08 '21 at 10:04

score 1 · Answer 2 · answered Apr 08 '21 at 16:45

I tend to prefer the PK be "natural". You have 3 columns that, together, can uniquely define each row. I would consider using it.

The next question is what order to put the 3 columns in. This depends on the common queries. Please provide them.

An index (including the PK) is used only leftmost first. It may be desirable to have some secondary key(s), for efficient access to other columns. Again, let's see the queries.

If you have a lot of secondary indexes, it may be better to have a surrogate, AUTO_INCREMENT "id" as the PK. Again, let's see the queries.

If you ever use a date range, then it is probably best to have DateSub last in any index. (There are rare exceptions.)

How many rows in the table?

The table is ENGINE=InnoDB, correct?

Reminder: The PRIMARY KEY is a Unique key, which is an INDEX.

DateSub is of datatype DATE, correct?

Composite primary keys vs auto increment primary key in sql workbench

2 Answers2