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.
I tend to favor the second approach because it is more extensible. I might add an additional field to the preferences table. Something like preference_order so that you can record someone's primary pick from a secondary one.
Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
