RE: Database design question
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <giovanni.cuccu@xxxxxxxxx>, "ORACLE-L" <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 9 Jan 2006 12:01:28 -0500
The topic you probably want to google is Bill of Materials (BOM), sometimes
also Bills of Materials (still BOM).
Prepare to be overwhelmed by complexity. Consider that each "atomic" part
may be itself an item for sale as well as a component part in varying
quantities of multiple subcomponents of other items for sale.
The "generic" solution is indeed a challenge. Each part may be available
from multiple different sources, possibly with different costs (including
shipping costs and taxes that may vary for a part that "costs" the same to
build).
As you peruse the available documentation of existing schema designs for
this problem, you'll probably want to pare down the noise to systems that
integrate order management, manufacturing, and store inventory management
(at a minimum).
Usually I favor the KISS method (Keep It Simple, Stupid). In the matter of
store inventories my experience is that dodging even little bits of the real
world complexity leads to iterative disaster. Say, do you have any interest
in currency conversions?
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Giovanni Cuccu
Sent: Monday, January 09, 2006 8:29 AM
To: ORACLE-L
Subject: OT: Database design question
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
