|
|
Posted by Hugo Kornelis on 04/27/06 00:45
On 25 Apr 2006 03:41:43 -0700, av1@email.t-com.hr wrote:
>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?
Hi Andro,
And the answer still remains that this is VERY easy if you use just one
table instead of 30 tables for the 30 piping systems.
In my previous message, I asked you to post the DDL (CREATE TABLE
statements, including all constraints and indexes) for the tables that
you are currently using. If you had done that, I could now have given
you the CREATE TABLE statement for the single table to replace your 30
current 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).
Okay. So what exactly does this mean for the sample population for two
of the current tables that I posted? Is that particular combination of
vallues logically permitted? If not, what column(s) need to be changed
and why?
>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.
Yes.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|