From: | "Craig Boucher" <craig(at)wesvic(dot)com> |
---|---|
To: | "'David G(dot) Johnston'" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Column order in multi column primary key |
Date: | 2016-08-08 20:35:02 |
Message-ID: | 0d8c01d1f1b4$5759b840$060d28c0$@wesvic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)g(dot)johnston(at)gmail(dot)com]
Sent: Monday, August 8, 2016 12:43 PM
To: Craig Boucher <craig(at)wesvic(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Column order in multi column primary key
On Mon, Aug 8, 2016 at 3:06 PM, Craig Boucher <craig(at)wesvic(dot)com <mailto:craig(at)wesvic(dot)com> > 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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-08-08 20:41:39 | Re: Column order in multi column primary key |
Previous Message | David G. Johnston | 2016-08-08 19:42:56 | Re: Column order in multi column primary key |