Re: Need schema design advice

On Sat, Oct 11, 2008 at 05:10:26PM +0000, Matthew Wilson wrote:
> I need to track employees and their preferred locations, shifts, and
> stations.

As always there's a trade off between "general" EAV style designs and
more specific ones (as noted by Jeff). One, more EAV style, design that
sprung to mind is:

CREATE TABLE preftypes (
preftype TEXT PRIMARY KEY
);

CREATE TABLE prefopts (
prefopt TEXT PRIMARY KEY,
preftype TEXT REFERENCES preftype
);

CREATE TABLE emps (
empid TEXT PRIMARY KEY
);

CREATE TABLE empprefs (
empid TEXT REFERENCES emps,
prefopt TEXT REFERENCES prefopts,
PRIMARY KEY (empid, prefopt)
);

INSERT INTO preftypes (prefname) VALUES
('location'), ('shift'), ('station');

INSERT INTO emps (empid) VALUES
('alice'), ('bob');

INSERT INTO prefopts (preftype, prefopt) VALUES
('location', 'west-side'),
('location', 'north-side'),
('shift', 'morning'),
('shift', 'night'),
('station', 'cash-register'),
('station', 'dishwasher');

INSERT INTO empprefs (empid, prefopt) VALUES
('alice', 'west-side'),
('alice', 'morning'),
('alice', 'cash-register'),
('bob', 'west-side'),
('bob', 'north-side'),
('bob', 'night'),
('bob', 'dishwasher');

you may want to move the "preftype" into the primary key of the
"prefopts" table; that would force you to reference it in the "empprefs"
table making queries asking for employee's preferences to specific
preftypes easier.

> create table preferences (
>
> employee_id int references employee (id),
> other_table_name text, /
> other_table_id int));

"other_table_name" sounds like bad style; no real way to enforce
integrity constraints (rules/triggers maybe, but it would be a bit of a
fiddle and prone to subtle bugs).

The scheme I gave should allow you to add new preference types, but it
makes it difficult to add details about the types' options. As always,
it's a trade off between what you're optimizing for. If you're adding
more preference types then go for a EAV style design, if you're going to
want to add more details about the preferences (this does seem to be the
common case, which is why most people here stay away from EAV designs).

This is easier to get started with, so if it's just going to be a quick
tech demo then this may be good. Be warned though that code from demos
tends to live much longer than you'd ever possibly expect so doing the
more long winded thing first may be easier--even a couple of months down
the line.

Sam