Composite Primary Keys and auto increment? What is a good practices?

Question

I'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways here,

1: increment "id" by myself (by trigger or from code)

or

2: Make "id" as auto-increment by mysql.

In first case i will have unique id's for each client, so each client will have id 1, 2, 3 etc.. In second case id's will grow for all clients. What is the best practice here? My priorities are: performace, security & scaling. Thanks!

Answer 1

You definitely want to use autoincrementing id values as primary keys. There happen to be many reasons for this. Here are some.

1. Avoiding race conditions (accidental id duplication) requires great care if you generate them yourself. Spend that mental energy -- development, QA, operations -- on making your SaaS excellent instead of reinventing the flat tire on primary keys.
2. You can still put an index on (client_id, id) even if it isn't the PK.
3. Your JOIN operations will be easier to write, test, and maintain.

4. This query pattern is great for getting the latest row for each client from a table. It performs very well. It's harder to do this kind of thing if you generate your own pks.

       SELECT t.*
         FROM table t
         JOIN (SELECT MAX(id) id 
                 FROM table 
                GROUP BY client_id
             ) m ON t.id = m.id