On Mon, Aug 8, 2016 at 1:47 PM, Craig Boucher <cr...@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 David J.
