Re: one table? five tables?

I'm sorry, but I can't see how the normalisation rules relate to your original complaint. I guess you need to spell it out for me!

Daniel

"Jeffrey Flaker" <jeffreyf_at_nac.net> wrote in message news:399865FF.96AF9510_at_nac.net...
> also look at: http://www.datamodel.org/NormalizationRules.html
>
>
> 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 Tue Aug 15 2000 - 20:32:45 CEST