Re: one table? five tables?

Take a look at: http://www.state.sd.us/people/colink/datanorm.htm

Daniel Dudley wrote:

> 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 - 23:31:29 CEST