Database design

[Anders]

We need to create a lookup structure that can contain a flexible amount of
attributes.

We have discussed different design solutions for lookup-functionality, and
come up with this suggestion:

The "lookup" structure will be defined by three tables. The "Element" table,
the "Attribute" table and the "Value" table.

I will specify with an example:
Lets say we want to define a lookup-element called vendor. First we insert a
row in the "Element" table. In the "Attribute" table we create one row for
every "information-piece" we want to be able to define for a vendor, e.g.
Name, number, country.
The "Value" table will contain the actual attribute-values, one row for each
value(3 rows for each vendor)

This creates a very flexible structure that can store many different types
of lookup-elements with different demands when it comes to number of
attributes("columns"). The structure supports an infinite number of
attributes.
On the downside this will potentially create a huge number of rows in the
"Value" table. Also the design concept is more abstract and where-statements
in query's might be more complex.

The alternative is the more usual structure with two tables. The "Element"
and the "Value" tables.
The "Value" table will then have one column for each attribute, and we would
specify as many columns as
we think we will ever need.
Any comments, thoughts and suggestions would be appreciated.

[Ed prochak]

"Anders" <an*************@news.com> wrote in message news:<UJ******************@news2.e.nsc.no>...

We need to create a lookup structure that can contain a flexible amount of
attributes.

We have discussed different design solutions for lookup-functionality, and
come up with this suggestion:

The "lookup" structure will be defined by three tables. The "Element" table,
the "Attribute" table and the "Value" table.

I will specify with an example:
Lets say we want to define a lookup-element called vendor. First we insert a
row in the "Element" table. In the "Attribute" table we create one row for
every "information-piece" we want to be able to define for a vendor, e.g.
Name, number, country.
The "Value" table will contain the actual attribute-values, one row for each
value(3 rows for each vendor)

This creates a very flexible structure that can store many different types
of lookup-elements with different demands when it comes to number of
attributes("columns"). The structure supports an infinite number of
attributes.
On the downside this will potentially create a huge number of rows in the
"Value" table. Also the design concept is more abstract and where-statements
in query's might be more complex.

The alternative is the more usual structure with two tables. The "Element"
and the "Value" tables.
The "Value" table will then have one column for each attribute, and we would
specify as many columns as
we think we will ever need.
Any comments, thoughts and suggestions would be appreciated.

you seem to want to create a relational DB on top of ORACLE.

WHY would you want to do this when ORACLE already does it so much better?

So IMHO, it is a bad idea.

Hey, you asked.8^)

have a good day,
ed

[Chris Leonard]

I have seen situations where this kind of design was used to capture generic
data modelling requirements, such as how many and what type of items would
appear on an exam question, or how many steps were required for a
manufacturing process, and in what sequence.

However, by *entirely* genericizing your schema, you will lose all sorts of
Oracle functionality, not the least of which is the ability to optimize
semantically different queries in different ways. Furthermore, any query
that cannot efficiently use indexes will scan *all* your data to get
whatever results are required.

So while this may be an interesting idea to you in principle, it may be
painful to live with unless your database is very, very small.

--
Cheers,
Chris

___________________________________

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, OCP, CIW
___________________________________

"Anders" <an*************@news.com> wrote in message
news:UJ******************@news2.e.nsc.no...

We need to create a lookup structure that can contain a flexible amount of
attributes.

We have discussed different design solutions for lookup-functionality, and
come up with this suggestion:

The "lookup" structure will be defined by three tables. The "Element" table, the "Attribute" table and the "Value" table.

I will specify with an example:
Lets say we want to define a lookup-element called vendor. First we insert a row in the "Element" table. In the "Attribute" table we create one row for
every "information-piece" we want to be able to define for a vendor, e.g.
Name, number, country.
The "Value" table will contain the actual attribute-values, one row for each value(3 rows for each vendor)

This creates a very flexible structure that can store many different types
of lookup-elements with different demands when it comes to number of
attributes("columns"). The structure supports an infinite number of
attributes.
On the downside this will potentially create a huge number of rows in the
"Value" table. Also the design concept is more abstract and where-statements in query's might be more complex.

The alternative is the more usual structure with two tables. The "Element"
and the "Value" tables.
The "Value" table will then have one column for each attribute, and we would specify as many columns as
we think we will ever need.
Any comments, thoughts and suggestions would be appreciated.