| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database design question
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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
--
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 09 2006 - 18:01:28 CST
 |
 |