Database design

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. Received on Thu Jul 29 2004 - 11:50:41 CEST