One or two tables?

[Phil]

I have numerous OPERATIONS. Each operation can have from 0 to a dozen or so
QUALIFERS. QUALIFIERS are unique to each OPERATION (ie. QUALIFIERS are not
repeated). I'm guesing that this should all be in one table. Is that the
best approach?

Thanks.

 

[tina]

suggest you read up on normalization principles (see
http://home.att.net/~california.db/tips.html#aTip1), then consider the
following in that context: one operation may have many qualifiers. that
right there tells you that qualifiers belong in a child table, linked back
to operations table. go on from there to review your process within the
context of normalization principles, and work out the tables/relationships.
you can always post back if you get stuck, or if you would just like someone
to review the structure you come up with, and make suggestions.

hth

 

[Phil]

I originally created two tables, but am now rethinking that. In my
OPERATIONS table I have a record that has the QUALIFIERID which refers to
another table where the QUALIFIERID refers to the QUALIFIER. If the QUALIFER
(or QUALIFIERID) is never repeated what's the point in having it in a second
table? Either way, the OPERATIONS table will have to have multiple
OPERATIONS records if it that operation has more than one QUALIFIER. Guess
I'm just slow.

 

[John W. Vinson]

I originally created two tables, but am now rethinking that. In my
OPERATIONS table I have a record that has the QUALIFIERID which refers to
another table where the QUALIFIERID refers to the QUALIFIER. If the QUALIFER
(or QUALIFIERID) is never repeated what's the point in having it in a second
table? Either way, the OPERATIONS table will have to have multiple
OPERATIONS records if it that operation has more than one QUALIFIER. Guess
I'm just slow.

The foreign key goes in the Many side table. You wouldn't have a QUALIFIERID
field in the Operations table; instead you would have an OperationID field in
the Qualifier table! This lets you enter one Operations record, and link it to
an arbitrary number of Qualifier records.

John W. Vinson [MVP]

 

[scubadiver]

It would be better to have a child table because each operation doesn't have
a set number of qualifiers but then it really depends what you want to do
with your qualifiers once you have entered the information. It can be very
difficult to manage and find the information you need if they are entered
into separate fields within a single table rather than as records within a
single field. You have to be careful!

What is an "operation" and what is a "qualifier" ??

 

[Phil]

I'm new to all this so I'm not familiar with all the expressions used.

I thought about this today and it's like the store example MS uses in their
course with Categories and Products. An OPERATION might be Heat Treating and
a QUALIFIER would be the type of Heat Treating, ie. HT009, HT010, HT017, etc.
Another example would be an OPERATION like Coating and QUALIFIERS like
Nickel, Chrome, Carbide, Hastalloy, Phosphate, etc. The QUALIFIER Nickel
would never be used with the OPERATION Heat Treating and likewise the
QUALIFIER HT009 would never be used with another OPERATION like Coating

Thanks for the help.

Phil

 

[scubadiver]

What I would have is a table for operations. In the design view, make the
first row a number and click on the key to make it a primary key (but don't
change the properties). Add all the extra fields you need and change the
properties as appropriate.

For qualifiers table, give the first field the same name and data type as
the primary key and add all the extra fields you need.

In the "relationships" view, click on the two tables and drag the primary
key field (which will be highlighted) over to the other table and create a
1-to-many relationship.

You can then use the wizard to create a form and subform.