Обсуждение: 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. Thanks in advance! Matt
> 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
On Sat, Oct 11, 2008 at 1:10 PM, Matthew Wilson <matt@tplus1.com> wrote: > 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 I'm certainly not an expert, but hopefully my commentary will still be somewhat helpful. Your "method 2" is something called an Entity-Attribute-Value table design[1]. There was a discussion on this list a couple weeks ago about the merits and drawbacks of designing your tables this way. Honestly, it probably depends on what your ultimate needs are. As the "Downsides" section of the Wiki link [1] shows, most of the problems with EAV really start to emerge when the tables get huge and you're dealing with hundreds of thousands to millions of entities, each with potentially hundreds of attribute-value pairs. If you're intending to roll out your application for every Starbucks on your continent, that might start to be a problem. (From my experience, implementations like this over large data sets suffer a big performance hit and carry a lot of data integrity baggage.) If you're talking about something for use in your chain of three internet cafes around one town, and you aren't going to have more than a dozen Attributes per Entity, it probably doesn't matter, because the complications will be more manageable without screwing something up. That said, by going the EAV/"Method-2" route, you're gaining flexibility, but at the cost of increased complication, and ultimately repurposing a relational database to do something that isn't very database-like, that's really more like a spreadsheet. (So why not just use a spreadsheet?) You have little room for recording additional information, like ordering preferences, or indicating that (say) a station preference depends on a location preference, or that a shift time depends on day of the week, etc -- so you're probably not getting as much flexibility as you think. Sure, you could add an "Extra_Data" column, so you have rows: Marie-Location-West-1, Marie-Location-East-2, Marie-Shift-Evening-Tuesday, Marie-Station-Register-West, Marie-Shift-Morning-Sunday, etc. But you can see the data integrity nightmare already, when you somehow manage to record "Marie-Shift-Register-1". Not to mention that you'll have to do format conversions for that "Extra_Data" field, and incorporate logic somewhere else in your program that deciphers whatever's in the generic data field to come up with ordering preferences for locations, station preferences by shift times, or whatever else you want to store. Essentially, in my humble opinion, you're putting off the problem of thinking about the nature and structure of your data, and most importantly, what you're going to use that data for, when ultimately those are the decisions that should be guiding how you design and use the database. Particularly given that this sounds like a management-efficiency project rather than one that your business (I'm assuming it's your business) hinges upon, I am imagining that you have time to be sure about exactly what you want to do with the data. If you expect that your business needs will change dramatically over the lifetime of the product, or you don't have time to make these decisions now, then maybe the flexibility outweighs the drawbacks. Good luck! [1] See e.g. http://en.wikipedia.org/wiki/Entity-Attribute-Value_model
Jeff, this is *exactly* the kind of feedback I was hoping to get. Thanks so much for the link and the explanation. Matt
knee deep in a schema design myself ..curious as to which advice did jeff offer for schema design?
thanks
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> To: pgsql-general@postgresql.org
> From: matt@tplus1.com
> Subject: Re: [GENERAL] Need schema design advice
> Date: Sun, 12 Oct 2008 13:55:42 +0000
>
> Jeff, this is *exactly* the kind of feedback I was hoping to get.
> Thanks so much for the link and the explanation.
>
> Matt
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
See how Windows connects the people, information, and fun that are part of your life. See Now
thanks
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> To: pgsql-general@postgresql.org
> From: matt@tplus1.com
> Subject: Re: [GENERAL] Need schema design advice
> Date: Sun, 12 Oct 2008 13:55:42 +0000
>
> Jeff, this is *exactly* the kind of feedback I was hoping to get.
> Thanks so much for the link and the explanation.
>
> Matt
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
See how Windows connects the people, information, and fun that are part of your life. See Now
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
On Sun, Oct 12, 2008 at 08:12:40PM +0100, I wrote: > 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)[...] oops, got distracted and forgot to finish this sentence! ...then you're better off with a non-EAV style design. Sam
could you provide a brief explanation of EAV ?
thx
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> Date: Sun, 12 Oct 2008 20:22:14 +0100
> From: sam@samason.me.uk
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Need schema design advice
>
> On Sun, Oct 12, 2008 at 08:12:40PM +0100, I wrote:
> > 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)[...]
>
> oops, got distracted and forgot to finish this sentence!
>
> ...then you're better off with a non-EAV style design.
>
>
> Sam
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Stay up to date on your PC, the Web, and your mobile phone with Windows Live. See Now
thx
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> Date: Sun, 12 Oct 2008 20:22:14 +0100
> From: sam@samason.me.uk
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Need schema design advice
>
> On Sun, Oct 12, 2008 at 08:12:40PM +0100, I wrote:
> > 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)[...]
>
> oops, got distracted and forgot to finish this sentence!
>
> ...then you're better off with a non-EAV style design.
>
>
> Sam
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Stay up to date on your PC, the Web, and your mobile phone with Windows Live. See Now
On Oct 12, 2008, at 5:51 PM, Martin Gainty wrote:
could you provide a brief explanation of EAV ?
Instead of:
create table vehicles
(
kind text primary key,
wheels int
);
insert into vehicles (kind, wheels) values ('car',4);
insert into vehicles (kind, wheels) values ('bike',2);
create table boats
(
kind text primary key,
displacement int
);
insert into boats (kind,displacement) values ('small boat',1000);
insert into boats (kind,displacement) values ('big boat',300000);
... in an EAV model you would do something like:
create table eav
(
kind text primary key,
attr text,
value text
);
insert into eav (kind, attr, value) values ('car','wheels','4');
insert into eav (kind, attr, value) values ('bike','wheels','2');
insert into eav (kind, attr, value) values ('small boat','displacement','1000');
insert into eav (kind, attr, value) values ('big boat','displacement','300000');
On Sun, Oct 12, 2008 at 8:10 PM, Ben Chobot <bench@silentmedia.com> wrote: > On Oct 12, 2008, at 5:51 PM, Martin Gainty wrote: > > could you provide a brief explanation of EAV ? > > (...) in an EAV model you would do something like: > create table eav > ( > kind text primary key, > attr text, > value text > ); > insert into eav (kind, attr, value) values ('car','wheels','4'); > insert into eav (kind, attr, value) values ('bike','wheels','2'); > insert into eav (kind, attr, value) values ('small > boat','displacement','1000'); > insert into eav (kind, attr, value) values ('big > boat','displacement','300000'); Truly Hideous (TM). Martin, I recommend you read the following: http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html
Martin Gainty wrote: > could you provide a brief explanation of EAV ? Please avoid HTML and eschew top-posting. The post from Jeff Soules in this thread included the advice: > See e.g. http://en.wikipedia.org/wiki/Entity-Attribute-Value_model which points to an explanation. -- Lew