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)


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! Same question at stackoverflow with more answers

  • Please link to the outer place where you asked the same question.
    – Rick James
    May 29 '16 at 1:05

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key.

In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.