Re: one table? five tables?

"ªL¨Î§»" <s8423022_at_cc.ncu.edu.tw> wrote in message news:8n515u$5ge$1_at_news.ncu.edu.tw...
>
> 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 questions 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.

CONCEPTUAL OUTLINE:     A product consists of:

        One or more components that require:
            Zero or more work processes.

TABLE RELATIONSHIPS:     Product -> (1-M)

        Component -> (1-M)
            Work_process

All tables require a primary key.
Component requires a foreign key to Product. Work_process requires a foreign key to Component.

Thus, when a product is selected, all of its components are accessible through its primary key, which is a foreign key in the Component table. Similarly, for each component selected the work processes are accessible via the component's primary key, which is a foreign key in the Work_process table.

If a particular component is provided by a vendor/contractor, which eliminates any in-house work processes, then no Work_process rows (records) will be found (selected) for that component.

But what if a component consists of other components? Well, this requires a recursive relationship, ie. a 1-M relationship which points to the same table. In other words, the Component table also carries foreign keys to itself (rows in the Component table). Similarly when a work process consists of other work processes. However, not all RDBMS allow direct recursive relationships, in which case you won't require a recursive foreign key in the table, but rather an extra table to handle every recursive relationship. For example, a component points (via its primary key) to zero or more rows in the Component2 table, and where a 'found' row contains a foreign key to a row in the Component table. This can, of course, cascade (component has components, which have components...). (Now isn't this just getting to be a lot of fun?) However, the real problem here isn't related to finding the cascading rows, but rather displaying or reporting/printing the returned data in an attractive and understandable fashion.

Finally, you should note that data relevant to product, component or work process are stored in their respective tables.

Daniel

PS. If this is homework, as your alias and email address suggests, then there's still some work for you to do before handing it in. ;-) Received on Sun Aug 13 2000 - 00:00:00 CEST