|
|
Posted by av1 on 04/25/06 13:41
I accept that this is bad database design.
(Any help toward improving it is appreciated, TIA)
But this is a "real world" example.
Designers are working on project consisting of several piping systems
(say 30) preparing (separate) bills of materials (BOMs) for each system
(water, oil, fuel, steam etc). BOMs looks similar to "TBL1_water" shown
at the beginning.
Later on - the project manager wants to know how many similar items we
have accross the project for purchasing purposes (discounts). He wants
to group similar items say ball valves or flanges for bidding purposes.
Regarding the table look - they use primarily Excel ( ** don't blame me
here ! ** )
I know that we need more advanced tool. That's why I raised this
question here.
Anyway, we can use this *.xls files more or less efficiently. (not to
mention problems with inconsistency with naming conventions, formats
etc. which exists ).
Question still remains: how to SELECT similar data of interest across
many tables?
Regarding the "identification_no": this data will be populated later on
after retrieving the data of interest (in question above), selecting
the supplier and after purchasing the items so it is not important in
the initial stage.
It is unique mark for the same item and same type across all the tables
(Say
ball valve with size 1", rated pressure 150#, manually operated and
made of bronze has assigned it's own identification_no (at
will) across the all tables. Similar valve type (ball) with all the
items the same except one (say different material ) has different
indent_no).
Take a shoe store example:
Maker Model size color ident_no
---------------------------------------------------------------------------
Clarks XY 11 black 1
Clarks XY 11 grey 2
Clarks XY 12 grey 3
etc...
(database guru would not prepare the table like this - this is only for
interpretation)
Generally, I assume that all the tables should be kept in one table
with one additional distinct column data representing the "system" at
every corresponding row.
Navigation:
[Reply to this message]
|