Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: OT: Database design question

Re: OT: Database design question

From: Giovanni Cuccu <>
Date: 2006-01-10 09:20:00

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,


On 1/9/06, Stephane Faroult 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).
> Stéphane Faroult

Another free oracle resource profiler Now version 0.9
Received on Tue Jan 10 2006 - 09:20:00 CST

Original text of this message