Re: Column order in multi column primary key

["Craig Boucher" <craig@xxxxxxxxxx>]

Thanks David.  I’ve thought about the hierarchy you mentioned but the hierarchy can change and I need to capture it as it was when the data was generated.

 

I should have pointed out in my last response that I was wondering if the performance of the pk index on work_session would be better if my primary key was (customer_id, work_session_id) or if (work_session_id,  customer_id) will be fine.  Customer_id will be repeated quite a bit in the table but work_session_id should be unique across the whole table.

 

Thanks,

Craig

 

From: David G. Johnston [mailto:david.g.johnston@xxxxxxxxx]
Sent: Monday, August 8, 2016 12:43 PM
To: Craig Boucher <craig@xxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Column order in multi column primary key

 

On Mon, Aug 8, 2016 at 3:06 PM, Craig Boucher <craig@xxxxxxxxxx> wrote:

Here is one of the tables that can have millions of rows and foreign key constraints to 5 other tables.

 

CREATE TABLE public.work_session

(

  work_session_id integer NOT NULL DEFAULT nextval('worksession_worksessionid_seq'::regclass),

  customer_id integer NOT NULL,

  store_id integer NOT NULL,

  department_id integer NOT NULL,

  station_id integer NOT NULL,

 

 

CREATE TABLE public.department

(

  department_id integer NOT NULL DEFAULT nextval('department_departmentid_seq'::regclass),

  customer_id integer NOT NULL,

 

 

No low-hanging fruit that I can see.

 

This is just some thought food - much 

would depend on the usage patterns of the table and the exact relationship between the different attributes.  Specifically, you've modeled this as an intersection of 4 orthogonal aspects (customer, store, department, station) while in reality these items likely exist in a strict hierarchy. It would

​be ​

possible to have the only FK on work_session be (station_id, customer_id) - and have station have the FK (department_id, customer_id) - and so forth.

 

David J.