Re: one table? five tables?

And your (constructive) suggestion is...?

Daniel

"Jeffrey Flaker" <jeffreyf_at_nac.net> wrote in message news:39974307.17F2AFC6_at_nac.net...
> Design is primarily up to the designer. Remember one thing: Your
 design
> should allow you to modify or delete some information without losing
 other
> valuable information. If you want to keep an archive of workprocess
 but
> want to delete other items completely, with your suggestion ALL
 would be
> lost.
>
> Daniel Dudley wrote:
>
> > "ª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
 dentifer( 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 Mon Aug 14 2000 - 17:46:24 CEST