one table? five tables?

From: (wrong string) ªL¨Î§» <s8423022_at_cc.ncu.edu.tw>
Date: 2000/08/13
Message-ID: <8n515u$5ge$1_at_news.ncu.edu.tw>#1/1


        A `product' is made by it's `component' and a `component' is made by several `work process'.

        I think I may need five tables in the database, but is this a good design???

        Tables:

  1. product(pid,name) primary key(pid)
  2. component(pid,cid) primary key(pid,cid) foreign key(pid) reference product.pid foreign key(cid) reference component.cid
  3. component_item(cid,name) primary key(cid)
  4. work_process(pid,cid,wid,step) primary key(pid,cid,wid,step) foreign key(pid,cid) reference component(pid,cid) foreign key(wid) referenc work_process_item(wid)
  5. work_process_item(wid,name) primary key(wid)

        it seems that there are too many tables and too many identifer( ex. pid, cid, wid...)

        and the few fields in each table (for example, component_item, work_process_item,...).

        The requirements are quite simple.
        I just want to know what work process are needed to produce a
product,
        and what components make up a product, and each steps to make a
component.
        because the requirements are quite simple, sometimes,  I think that
maybe there are too many tables.
        maybe we could put all the things in one big table? like this
        (id, product, component, step, work_process)


        The question are:
            1. is this a good design?
            2. should I use the component' name as it's identifer and delete
the cid field from component_item table?
                and use work process' name to identifer work_process_item
but not wid?
            3. if the answer of questoin 2 is YES,
                should I combine work_process_item and component_item into
other tables, or one field in one table is good?
        any comment would be appreciated.
        thanks a lot.
Received on Sun Aug 13 2000 - 00:00:00 CEST

Original text of this message