Re: Database design
From: Chris Leonard <s_p_a_m_chris_at_hotmail.com>
Date: Fri, 30 Jul 2004 12:59:11 -0500
Message-ID: <P%vOc.214$Qc.3995_at_news.uswest.net>
Date: Fri, 30 Jul 2004 12:59:11 -0500
Message-ID: <P%vOc.214$Qc.3995_at_news.uswest.net>
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" <answer_to_group_at_news.com> wrote in message news:UJ3Oc.5451$v04.83560_at_news2.e.nsc.no...Received on Fri Jul 30 2004 - 19:59:11 CEST
>
> 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.
>
>
>