From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Craig Boucher <craig(at)wesvic(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Column order in multi column primary key |
Date: | 2016-08-08 19:42:56 |
Message-ID: | CAKFQuwYPzpvKkgOx9HrxYYYuDVTfsJiHiDCVE=kXALLeNzN7rw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Aug 8, 2016 at 3:06 PM, Craig Boucher <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 | Craig Boucher | 2016-08-08 20:35:02 | Re: Column order in multi column primary key |
Previous Message | Craig Boucher | 2016-08-08 19:06:56 | Re: Column order in multi column primary key |