Hello
________________________________
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Alex
Turner
Envoyé : lundi 4 février 2008 05:14
À : Lewis Cunningham
Cc : vladimir konrad; pgsql-general@postgresql.org
Objet : Re: [GENERAL] [OT] "advanced" database design (long)
I"m not a database expert, but wouldn't
create table attribute (
attribute_id int
attribute text
)
create table value (
value_id int
value text
)
create table attribute_value (
entity_id int
attribute_id int
value_id int
)
give you a lot less pages to load than building a table with say 90
columns in it that are all null, which would result in better rather than worse
performance?
Alex
On Feb 2, 2008 9:15 AM, Lewis Cunningham <[EMAIL PROTECTED]> wrote:
--- vladimir konrad <[EMAIL PROTECTED]> wrote:
> I think that I understand basic relational theory but then I
had an
> idea.
> Basically, instead of adding field to a table every time
there is a
> need for it, have a table split in two: one holds identity
(id) and
> one holds the attributes (linked to this id).
> Basically, if in the future user decides that the subject
should
> have a new attribute, he can simply add "attribute
definition" and
> attribute_definition_set (if any) and the application would
handle
Basically, you would be creating your own data dictionary (i.e.
system catalog) on top of the db data dictionary. The database
already comes with a way to easily add columns: ddl. I have
seen
newbie database designers reinvent this method a hundred times.
The
performance hits and complexity of querying data would far out
weigh
any perceived maintenance gain.
This model is known as Entity-Value-Attribute and not well appreciated by
relational designers. I think it is not relational, but I use it as storage for
data (in some case, I don't know the database structure -relational- where data
will be stored). It's like a truck container used for office removal :).
Imho, don't use it with a complex database structure; as a minimum, use lookup
tables for Value (to avoid uncontrolled new parameters) and add a column to
store the attribute type.
Jacques Massé
