Re: Need schema design advice

> 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