From: | Matthew Wilson <matt(at)tplus1(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Need schema design advice |
Date: | 2008-10-11 17:10:26 |
Message-ID: | slrngf1ng1.po9.matt@sprout.tplus1.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I need to track employees and their preferred locations, shifts, and
stations.
For example, I need to track that Alice prefers to work the morning
shift at the west-side location, and she likes to work the cash-register
station.
Also, I need to track that Bob likes the west-side and north-side
locations, likes the night shift, and likes the dishwasher station. Note
the one-to-many relationship between Bob and his preferred locations. I
need to support that possibility.
So, I see two ways to make my tables, and I'd like some advice.
FIRST METHOD:
create table preferred_location (
employee_id int references employee (id),
location_id int references location (id));
create table preferred_shift (
employee_id int references employee (id),
shift int references shift (id));
create table preferred_station (
employee_id int references employee (id),
station_id int references station (id));
SECOND METHOD:
create table preferences (
employee_id int references employee (id),
other_table_name text, /
other_table_id int));
In the second method, I'd store tuples like this in the preferences
table:
(<Alice's ID>, 'location', <west-side location ID>),
(<Alice's ID>, 'shift', <morning shift ID>)
(<Alice's ID>, 'station', <cash register station ID>)
The nice thing about the second approach is I can extend this to store
all sorts of preferences as I dream them up. But on the downside, I
don't have any FK constraints.
I suspect this is a pretty common dilemma. Any commentary from the
experts on this list is welcome.
Thanks in advance!
Matt
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-10-11 17:29:01 | Re: Starting PostgreSQL |
Previous Message | Adrian Klaver | 2008-10-11 16:39:01 | Re: Starting PostgreSQL |