Re: OT: Database design question
- From: Giovanni Cuccu <giovanni.cuccu@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Tue, 10 Jan 2006 09:20:00 +0100
Hi Stephane, Jared, Nuno
thanks fot the input. At the moment I'm in the evalutation phase
(I can still say 'no thanks" to the customer), and what I'm evaluating
pro and cons of some solutions.
After reading your replies I'm more convinced tha a "generic database"
is a database that does not work (also because of some other non
technical aspects of this project).
I was considering the solution given by Stephan, i.e. using a generic
table and having a specific table for each different item type, the
main advantage is that I already developed a similar solution and I
have some reusable production code.
The main drawback with this solution is that a simple table is not
enough, since the specific item coud have more than one specific
attribute (for example a pc could have two different hard drives);
because of this I was investigating the bill of material design in
order to understand what is the best compromise among the two
opposite design solutions.
Thanks again,
Giovanni
On 1/9/06, Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote:
> Giovanni,
>
> You are on a slippery slope. You have identified quite correctly one
> of the issues (and someone pointed at performance too). But you should
> also tell your customer that any type of so-called "meta" design means
> that you won't be able to implement foreign keys nor integrity
> constraints (unless you bend over backwards in triggers). For instance,
> some of your item attributes will be numbers, some characters, which
> means that everything would have to be stored as a string. If someone
> mistypes a O (letter) for a 0 (digit), no way to check it. Consequence,
> queries may return wrong results. Even if you try to segregate the
> values by type, you will have no way to check that values are valid or
> within reasonable bounds. Unless most of the code is made of validity
> checks in the application code (no protection against the fat-fingered
> SQL*Plus user).
>
> It's a much better solution to have a generic ITEMS table, and a
> specific table by type of item (subtyping).
>
> HTH
>
> Stéphane Faroult
>
--------------------------------------------------------------------
Another free oracle resource profiler
http://sourceforge.net/projects/oraresprof/
Now version 0.9
--
//www.freelists.org/webpage/oracle-l
Other related posts:
