On Mon, Aug 8, 2016 at 1:47 PM, Craig Boucher <craig@wesvic.com> wrote:
PG 9.5
I’m in the process of converting our application from Sql Server to Postgresql. I’m taking advantage of this process to make some database design changes.
Our database contains data for many customers and I have a customer_id column in every customer related table. One of the changes I made was to change all of the tables that use an auto incrementing number as the primary key to also include a customer_id in the pk. I also changed all of the foreign keys to include both the id number column and the customer_id. I made this change so when inserting a child record, I don’t have to look up each parent record to verify that it is owned by that customer. The database handles this check for me now through foreign key constraints. My question about multi column primary keys is should I have the customer_id column first (which will have many repeated rows) and then the auto incrementing id field (which will most likely be unique in the table), or should it be there other way around. Will the pk index perform better one way or the other or will it no matter?
The number of customers are in the hundreds and the number of child records in some of the tables can be in the millions.
The PK should be (child, parent) - selectivity is the most important aspect of the PK.
You will also want an index on just (parent) to support its FK nature.
But, your description seems lacking...I'd suggest you show an example set of tables with names, PKs and FKs
