Re: One-to-Many Help

On Mon, 25 Jul 2005 19:42:02 -0700, "Steve B."
<SteveB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>I have about 3500 Part Numbers (PN's). Each PN has about 10 "base" type
>properties (Customer, etc) that I put in 10 different table columns. No
>problem. However, new business requirements require that each PN be provided
>with 30 possible characteristics with each of the 30 characteristics
>containing 4 different creteria. So where talking 10 + (30 x 4) = 130
>columns for one PN.

Nope, you're not. "Fields are expensive, records are cheap"!

You're talking about a Many (parts) to Many (characteristics)
relationship, which requires three tables:

Parts
PN
<ten or so fields of part specific data>

Characteristics
CN Primary Key
Description Text
<maybe some other fields describing the characteristic>

PartCharacters
PN <foreign key to Parts>
CN <foreign key to Characteristics>
CharValue Text <the value of this characteristic for this part>


John W. Vinson[MVP]
.

Relevant Pages

  • RE: DB Design for a Service Company...
    ... Clicking the + shows all sites for each customer. ... Simple table detailing supplier names, addresses, product/service provided. ... which will contain the same number as the primary key (from ... you use the same name for the foreign key as you used for the primary key. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Get data from combo box to popluate the next box
    ... corresponding table by linking the tables in a query by the common field. ... When the customer is entered on the form via the ... If data is entered into the control, ... the corresponding foreign key field in the underlying table. ...
    (microsoft.public.access.forms)
  • Re: Help with Delete query
    ... created the foreign key between the tables in the first pace, as this is exactly what the foreign key is there ... You can create a "dummy" customer and update the order so they refer to that dummy customer. ... In my example, Orders is the referencing table, ... >>> Diego F. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with Delete query
    ... I would not want to have orders for which I don't know which customer to ship them to. ... foreign key, ... > that dummy customer. ... >>> Diego F. ...
    (microsoft.public.sqlserver.programming)
  • Re: Reference table
    ... way to normalize it would be to create normalized tables (a Customer ... queries from the wide-flat table into each of the "one" side tables, ... record for each customer or for each part, ... you'll then need to add foreign key fields to the original table ...
    (microsoft.public.access.tablesdbdesign)