From: | Rodrigo E(dot) De León Plicet <rdeleonp(at)gmail(dot)com> |
---|---|
To: | Andreas <maps(dot)on(at)gmx(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Funny foreign keys |
Date: | 2009-04-30 02:04:52 |
Message-ID: | a55915760904291904w7f331effq60ba32d317a6bbff@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Wed, Apr 29, 2009 at 9:57 AM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> Hi,
>
> I'd like to use a lookup-table that contains text-values and their keys.
>
> There is a data-table with entities that have (among others) 2 attributes
> which could nicely be represented by numbers.
> e.g. enttity (e_id, ..., color_fk, shape_fk, ...)
>
> Now I could create 2 tables with the text:
> color (-1, 'not provided'), (0, NULL), (1, 'red'), (2, 'green'), (3,
> 'blue'), ...
> shape (-1, 'not provided'), (0, NULL), (1, 'point'), (2, 'line'), (3,
> 'triangle'), (4, 'circle'), ...
>
> There might come more such attributes.
> Therfore I'd rather have just 1 attribute-table with a 3rd column that has a
> group-id:
> attributes (attribute_id, attribute, attribute_group)
> (-1, 'not provided', 0), (0, NULL, 0),
> (100, 'red', 1), (110, 'green', 1), (120, 'blue', 1), ...
> (200, 'point', 2), (210, 'line', 2), (220, 'triangle', 2), (230, 'circle',
> 2), ...
>
> Easy:
> Within the entities-table I can define 2 fk-constraints on color_fk,
> shape_fk which reference the cumulative attribute-table.
> The application should make sure that the user could only chose from group 0
> (shared) and one other.
>
> Difficult
> I'd like to let the DB control this to not risk that shape-ids end up in the
> color_fk.
> How can I make shure, that color_fk only receives values from
> attribute-groups 0 and 1 but no other?
>
This smells like EAV.
Please read:
http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html
... and consider reevaluating the schema according to valid relational
design (tables, columns, check constraints, etc.).
In any case, good luck.
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2009-04-30 02:28:54 | Re: retrieving primary key for row with MIN function |
Previous Message | Adam Ruth | 2009-04-29 22:20:38 | Re: retrieving primary key for row with MIN function |