Re: OT: Database design question

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: giovanni.cuccu@xxxxxxxxx
  • Date: Mon, 09 Jan 2006 18:12:01 +0100


   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
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
> Now version 0.9
> --


Other related posts: