One-to-Many Help

[Steve B.]

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.

While some of the columns have a ComboBox from a separate table query most
of the data varies and is technical (string/text). Right now 130 column
gives me a "Property value is too large" error. How do I design the dB?
I'm also have to revise my ADO.Net interface too.

Steve

 

[Brian]

Here is one way: instead of having 130 FIELDS in one table, have a separate
RECORD in the PartCharacteristics table for each part/characteristic
combination. You could do this with perhaps four basic tables (and using your
terminology):

1. Parts table, having two fields: PartNumber (Primary Key) and Description
2. Characteristics table, having one field: Characteristic (PrimaryKey)
3. Criteria table, having two fields: Characteristic (relationship to
Characteristics.Characteristic) and Criteria
4. PartCharacteristics table, three fields: PartNumber (relationship to
Parts.PartNumber), Characteristic (relationship to
Characteristics.Characteristic), and Criteria (relationship to
Criteria.Criteria)

This will require more programmatic manipulation, particularly filtering on
the required forms, but the basics are thus:

First, build a table of characteristics. Second, build a table of Criteria
valid for each characteristic. Finally, as you build your PartNumber table,
have a form where you can add the PartNumber with a subform that allows you
to select characteristics/criteria for that part.

Just an idea...

 

[John Vinson]

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]

 

[Steve B.]

John,

Thank you all for responding.

Do I really have a many-to-many? I have one PN going to 120 characteristics
but I don't have that same 120 characteristics going to many different PN's.
The 120 (30 x 4) characteristics are shown on the blueprint (drawing) for the
Part. I thought many-to-many had to work both ways. Sorry for not fully
defining my requirements. If I do have a many-to-many or, if this is the
best way to handle it - as I understand it:

1st table (Parts) would have
(a) the parts and the specific part data (the 10 columns).
(b) Primary key is an autonumber type (e.g. PN).

2nd table (Characteristics)
(a) contain all the blueprint characteristics (120 columns)
(b) Primary key is autonumber (e.g. CN)

3rd table (PartCharacters)
(a) Primary key is autonumber type
(b) contain the PN and CN as forign keys
(c) contain the PartsData and the CharValue Text from table one and two

I need to do some research on that 3rd table.

Please comment

Steve




the second table would be full of the 120 characteristics and the

 

[Brian]

Are you saying that each part has 120 characteristics that are different in
NATURE from the characteristics used for other parts, or is it just the
AMOUNT attached to each characteristic that varies by part? (Pardon the caps
for emphasis, but the forum does not do bold/italics.) My guess is that it is
the latter, but correct me if I am wrong.

Assuming this to be true, the Characteristics table would not contain 120
FIELDS (columns), but 120 RECORDS - 1 for each characteristic. It could have
as little as two fields: CharID (Primary Key, AutoNumber) and CharDesc (short
description of the characteristic).

Characteristics table examples:

CharID = 1, CharDesc = "Width"
CharID = 2, CharDesc = "Height"
CharID = 3, CharDesc = "Material"

Depending on the relationship of characteristics to criteria, you could
stick with 30 records and add an additional field for each criteria: Crit1,
Crit2, Crit3, and Crit4 (that is, if the NAME of each criteria will be the
same for that particular characteristic, regardless of the part number to
which it applies; otherwise you will need to stay with 120 records).

Now, just a couple of follow-up questons:

1. Do the same 120 characteristics apply to each part (i.e. must each part
have an entry for each characteristic), or is this a pool of characteristics,
only some of which apply to any given part?

2. Does each characteristic also have an associated data element (e.g.
CharDesc = "Width", CharData = 4.25)?

If #2 is true, then the fields in PartCharacters table would be like this:

PartID (relationship to primary key of Parts table)
CharID (relationship to primary key of Characteristics table)
CharData (user-entered data giving the quantity associated with that
characteristic)

The key to having this work will be well-structured forms that make this all
transparent to the user. Now, for a simple example that shows the
many-to-many relationship:

Parts table:
PartID = 1, PartDesc = "Dial"
PartID = 2, PartDesc = "Lid"
PartID = 3, PartDesc = "Base"

Characteristics table:
CharID = 1, CharDesc = "Length"
CharID = 2, CharDesc = "Diameter"
CharID = 3, CharDesc = "Width"

PartCharacters table:
PartID = 1, CharID = 2, CharData = 1.25 (denoting a diameter of 1.25 for the
dial)
PartID = 2, CharID = 1, CharData = 14.75 (denoting a length of 14.75 for the
lid)
PartID = 2, CharID = 3, CharData = 10.25 (denoting a width of 10.25 for the
lid)
PartID = 3, CharID = 3, CharData = 17 (denoting a width of 17 for the base)



PartID 1 has more than 1 Characteristic, but CharID 1 is used in more than
one part.

One more thought (to further complicate matters): since you may have various
projects, you may well need to have a Projects table that has ProjID &
ProjDesc, and then add ProjID as a foreign key to your Parts table to
identify which project contains that particular part.

 

[Steve B.]

Brian

Thank You for responding and taking the time

NATURE, I think the best way to explain it is do what you did and give and
example

A single part is manufactured from a single blueprint. I have 3000
parts/blueprints. A blueprint can have 100's of dimensional characteristics
(radius, diameter, chamfer, etc) or, only a few, but we are limiting
ourselves to a possible 30 of the most important characteristics - critical
one's

Each of the possible 30 characteristics for each part has four creteria as
follows. Class, Location, Manufacturing Process and the Critical
Characteriistic. For Part Number 1234 the 1st of 30 characterisic's is
Class: I
Location: SYS2
Process: Drilling
Characterisic: Diameter 1.5"

2nd of 30 characterisic'sis:
Class: IV
Location: GOT5
Process: Milling
Characterisic: Radius .032"
..
..
The 30th of 30 Characteristic's is:
Class: II
Location: PIN7
Process: Rolling
Characterisic: Length 2.5"

ANOTHER Part Number is 4321 (only one characteristic - 1 of 1)
Class: V
Location: FIG12
Process: Peening
Characterisic: chamfer .08"

ANOTHER Part Number 6432 (five characteristic's)
1st of 5 characteristic'is:
Class: III
Location: DED9
Process: Hardness
Characterisic: 32 Rockwell
..
..
5th of 5 characteristic's is
Class: III
Location: POP23
Process: Heat Treat
Characterisic: 3000 degrees

Comments??

 

[John Vinson]

5th of 5 characteristic's is
Class: III
Location: POP23
Process: Heat Treat
Characterisic: 3000 degrees

Comments??

Looks to me like you've laid out a perfectly classic one to many
relationship. Your Characteristics table would have a foreign key to
the main table, and fields for Class, Location, Process and
Characteristic.

John W. Vinson[MVP]

 

[Steve B.]

So it's one-to-many and your recommending 2 tables one for part data (main
Table) and one for all the part characteristics (Characteristics table) .

Thank You John