Home
Reading
Searching
Subscribe
Sponsors
Statistics
Posting
Contact
Spam
Lists
Links
About
Hosting
Filtering
Features Download
Marketing
Archives
FAQ
Blog
 
Gmane
From: Matthew Wilson <matt <at> tplus1.com>
Subject: Need schema design advice
Newsgroups: gmane.comp.db.postgresql.general
Date: Saturday 11th October 2008 17:10:26 UTC (over 8 years ago)
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:

    (, 'location', ),
    (, 'shift', )
    (, 'station', )

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


-- 
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
 
CD: 3ms