Re: OT: Database design question

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  

On Mon, 2006-01-09 at 14:29 +0100, Giovanni Cuccu wrote:
> Hi all,
> a customer asked me for a "generic store" database. The term
> generic means that the database should be able to store variuos kind
> of items. An example could be an IT hardware shop where you can buy
> computers, printers, routers, etc. For each item the db should store
> the different parts (i.e. the data for a PC must contains the cpu
> type, hard disks, RAM installed, os type, etc)
> I was coming to the conclusion that the design that seems to solve the
> problems is something like this (I list the tables with their meaning)
> items contains item id, item_type and descrption
> items_metadata contains every possible attribute for each item_type
> items_attributes contains the item attributes (CPU, RAM,etc)
> attributes_metadata contains the attribute definition
> This is just the basic idea; the main problem (at least for me) is
> that a simple query like:
> give me all computers with WinXP and 512MB RAM
> involves a self join or the use of analytics.
> I googled for
> database design part
> database design inventory
> database design store
> database design warehouse
> but I did not found a different solution.
> Since I think this is a very common design problem does anyone has
> some reference or advice?
> Thanks a lot
> Giovanni
>
> --
> --------------------------------------------------------------------
> Another free oracle resource profiler
> http://sourceforge.net/projects/oraresprof/
> Now version 0.9
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l