Need schema design advice
От | Matthew Wilson |
---|---|
Тема | Need schema design advice |
Дата | |
Msg-id | slrngf1ng1.po9.matt@sprout.tplus1.com обсуждение исходный текст |
Ответы |
Re: Need schema design advice
Re: Need schema design advice Re: Need schema design advice |
Список | 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
В списке pgsql-general по дате отправления: