Table design question

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 28 Aug 2002 12:45:30 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702914AD5@lnewton.leeds.lfs.co.uk>

Hi Mike,

>> By watching people try to shoehorn parts, subassemblies, and
>> assemblies into a single table.

Intersting comment.
I've got no experience (yet) in this area, but how difficult can it be ?

Unless a sub-assembly or assembly have no unique part numbers, themselves, surely it's easy to fit them all into one table ?

Isn't this very similar to the scott.emp demo table where employees have managers, who are also employees and threfore listed in the emp table ?

Here's my first impression :

table stock_items is something like :

part_no (PK) which can be a part, assembly or sub-assembly. qty_in_stock
re_order_qty
minimum_stock_level
etc
etc

Then another table for the assembly details :

assembly_part_no (PK column 1)
item_part_no (PK column 2)
qty_required
etc
etc

And probably a description table if description was able to be normalised out of the main stock_items table.

So, all stock is in the stock_items table. Part lists for all assemblies can be extracted from the assembly table.

comments on why this isn't a good way of doing it are welcome.

Please be gentle with me !!!

Regards,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Received on Wed Aug 28 2002 - 06:45:30 CDT