Design Question involving composite table

[csolomon]

Hello:

I was wondering if I could get some input on how to address a design
issue, involving my composite table.

I have one portion of my project complete. The following forms and
reports I will add, piggyback off of my existing design.
The part I have already completed allows my users to create a design
sample; this is made up of several materials to create one sample. I
have accomplished this using this design:

tblMixDesign:
DM_Mix (PK), DM_SampleNO, DM_Dt, etc

tblMaterial:
materialID (PK), matTypeID (FK), material, materialGrav

tblMixSample (composite table joining tblMixdesign and tblMaterial)
DM_Mix (PK1), DM_MaterialNo (PK2), matTypeID (FK), materialID (FK),
matBatchWeight

both Mixdesign and tblMaterial have a one to many relationship with
tblMixSample.

My question is related to another table I believe I will need to join
to the composite table, tblMixSample (an of course its parent table
tblMixDesign). The table, tblMCorrections , is related to the
composite table in my opinion. The materials selected by the user in
the tblMixSample, are the same materials that will have information
related to them in the tblMCorrections table.
My issue is that when I tried to connect the tables, access would not
allow me to connect the primary keys. Is it because there are two
primary keys in the composite table?

Here is the tblMCorrections :
correctionsID(P K), DM_Mix (FK), corr_Dt (Date), corr_wetGs,
corr_dryGs, corr_absorption

This table relates to the others (MixDesign and MixSample) because
when the material type (matTypeID) is Fine or Coarse (selected in
tblMixsample), these materials are measured for their moisture. the
user will enter how much they measure the material weight to be wet,
dry, and also the absorption (all user inputs, stored in the
tblMCorrections ). As I stated i tried to link the MCorrections to
the MixSample, so as to carry over the material type (Fine and
Coarse), but was unable to do so because access would not allow me to
connect the keys. How can I illustrate the relationship if I can not
connect the keys?

[FishVal]

Hello, solomon.

tblMixDesign:
DM_Mix (PK), DM_SampleNO, DM_Dt, etc

tblMaterial:
materialID (PK), matTypeID (FK), material, materialGrav

tblMixSample (composite table joining tblMixdesign and tblMaterial)
DM_Mix (PK1), DM_MaterialNo (PK2), matTypeID (FK), materialID (FK),
matBatchWeight

First of all about the existing tables:

• tblMixSample.ma tTypeID is redundant field as long as tblMaterial.mat TypeID contains the same information.
• Why do you use composite PK in tblMixSample?

The rest makes little sense for me so far?
Could you elaborate on this.


Regards,
Fish

[csolomon]

Hi Fish,

I appreciate your response.

"First of all about the existing tables:
tblMixSample.ma tTypeID is redundant field as long as tblMaterial.mat TypeID contains the same information.
>>MatTypeID is in the mixType table because a user can select many matTypes for one or more MixDesigns...th e selection has to be made by the user.
Why do you use composite PK in tblMixSample?
>>a composite key is used because i needed to identify which record matched the record in MixDesign (DM_MIx) as well as just to have the individual records be identified in the MixSample (via the PK2). I wont ever use the PK2 to identify the records, but it was suggested to me to use the composite key.

What would you like to me to elaborate on? As I stated, I need to create a relationship that depicts these business rules (I already have the first two):

1)Each DesignMix will have many materials./Many materials will be assigned to one or more DesignMixes
2)Each DesignMix will have many material types./ Many material types will be assigned to one or more DesignMixes
3)Each Mix Sample (tblMixSample) will have one or more Moisture Corrections (tblMCorrection s)./Each Moisture Correction will be assigned to one or more Mix Samples

here is an illustration of what my relationships (1and 2) look like

DesignMix
DM_Mix/DM_sampleNo/DM_Dt/jobNumber
1/ 1984/ 12.15.2008
2/ 1985/ 12.01.2008

MatType
MatTypeID, MatType
1/Cement
2/Fine Aggregate
3/ Coarse Aggregate

Material
materialID/ matTypeID/ material
1/ 1 (Cement) / gravel1
2/ 1 (Cement)/ gravel2
3/ 2 (Fine)/ sand1
4/ 2 (Fine)/ sand2
5/ 3 (Coarse)/ coarse1

MixSample-composite of MixDesign and material
DM_MIx (PK1)/ DM_MaterialNo (PK2)/ matTypeID, materialID, matBatchWeight
1/ 1/ 1(Cement)/ 1 (gravel1)/ 15
1/ 2/ 1(Cement)/ 2 (gravel2)/ 2.03
1/ 25/ 2 (Fine)/ 3(sand1)/ 25
2/ 33/ 2 (Fine)/ 3 (gravel1)/ 44
2/ 32/ 2 (Fine)/ 4 (gravel2)/ 45
2/ 22/ 3 (Coarse)/ 5 (coarse1)/ 44
As you can see, many matTypes (cement, fine, coarse, etc) and many materials (gravel1, sand1, gravel2, sand2, coarse1) can be assigned to one or more DesignMixes

MCorrection is related to the DesignMix because there will be information recorded in the MCorrections table that relates to the certain material types selected, specifically Fine and Coarse matTypes.

My issue is how do I relate them in the tables? Do i need another composite table connecting MixSample (Mix Sample actually holds the material and material types related to the DM_Mix) and MCorrections?

[FishVal]

Hello, solomon.

"First of all about the existing tables:
tblMixSample.ma tTypeID is redundant field as long as tblMaterial.mat TypeID contains the same information.
>>MatTypeID is in the mixType table because a user can select many matTypes for one or more MixDesigns...th e selection has to be made by the user.

Does that mean mixType.MatType ID is not the same as tblMaterial.Mat TypeID for a given materialID value?

Why do you use composite PK in tblMixSample?
>>a composite key is used because i needed to identify which record matched the record in MixDesign (DM_MIx) as well as just to have the individual records be identified in the MixSample (via the PK2). I wont ever use the PK2 to identify the records, but it was suggested to me to use the composite key.

Why [DM_Mix] together with [DM_MaterialNO] are composite PK?
From your explanation [DM_MaterialNO] has a unique value.
So you either use [DM_MaterialNO] as PK, or [DM_Mix] and [materialID] as composite PK.

What would you like to me to elaborate on? As I stated, I need to create a relationship that depicts these business rules (I already have the first two):

1)Each DesignMix will have many materials./Many materials will be assigned to one or more DesignMixes
2)Each DesignMix will have many material types./ Many material types will be assigned to one or more DesignMixes
3)Each Mix Sample (tblMixSample) will have one or more Moisture Corrections (tblMCorrection s)./Each Moisture Correction will be assigned to one or more Mix Samples

1) A classic many-to-many relationship. You almost have this, just assign PK/FK fields in a proper way.
2) So, again, what material type is related to - material itself or material in a given design mix?
3) That makes a very little sense. For what moisture tests are being performed? Do they measure material moisture level or mixsample moisture level?

Kind regards,
Fish.

[csolomon]

"Does that mean mixType.MatType ID is not the same as blMaterial.MatT ypeID for a given materialID value?"
>>MixType.MatTy peID was a typo...I meant to say that MixTypeID is in the MixSample table because a user can have many mixTypesID and many MaterialIDs related to one sample.
The MatTypeID in the tblMaterial just illustrates that there can be many materials of the same MatType

Let me clearly state what a MixSample is: a combination of many material types and material make up one mixSample.

"Why [DM_Mix] together with [DM_MaterialNO] are composite PK?
From your explanation [DM_MaterialNO] has a unique value.
So you either use [DM_MaterialNO] as PK, or [DM_Mix] and [materialID] as composite PK."
>>The MixSample.DM_Mi x represents the DM_Mix (let's refer to it as Sample Number, as that is what is). DM_MaterialNo (PK) represents each individual record in the table, regardless of the sample number (DM_Mix) it belongs to. The joint primary key is what is used to identify the specific sample number the record belongs to. MaterialID could be used as a part of the composite key...
"1) A classic many-to-many relationship. You almost have this, just assign PK/FK fields in a proper way.
2) So, again, what material type is related to - material itself or material in a given design mix?
>>MixSample.mat TypeID is related to the MixDesign because each material type can be used more than once when making a sample. The user can then select the MixSample.mater ialID, based on the matTypeID that is selected.
3) That makes a very little sense. For what moisture tests are being performed? Do they measure material moisture level or mixsample moisture level?"
>>Moisture tests are measured on the coarse and fine materials in the mix sample.

[FishVal]

Ok.

Post please your tables metadata clearly outlining relationships and field datatypes.
Like in the sample below:

Table: [tblMaster]
keyMasterID, Long(Autonumber ), PK
txtMaster, Text

Table: [tblChild]
keyChildID, Long(Autonumber ), PK
keyMasterID, Long, FK(tblMaster)
txtChild

[csolomon]

table:MixDesign
DM_Mix, AutoNumber, PK
DM_SampleNo, Number
DM_Date, Date
jobNumber, Number, FK
panID, Number, FK
toneID, Number, FK
finishDepID, Number, FK
FinishActID, Number, FK
DM_pan, Number
DM_SackMix, Number
DM_waterRatio, Number
DM_entAir, Number
DM_Memo, Memo
DM_DtNeeded, Dt
DM_Requestor, text

Table: MixSample
DM_Mix, Number, PK1
DM_MaterialNo, AutoNumber, PK2
matTypeID, Number, FK
materialID, FK
matBatchWeight, Number
pigPercent, Number

Table: MatType
matTypeID, Number PK
matType, Text

Table: Material
materialID, Number, PK
matTypeID, Number, FK
material, Text
materialGrav, Number

Table: MCorrections
correctionsID, AutoNumber, PK
DM_Mix, DM_materialNo, Number, composite FK
CorrDt, Date
corrDryGs, Number
corrWetGs, Number
corrAbsorption, Number

I believe that this design may work, but I am not sure. Each of the materials in the MixSample table (per SampleNo, or DM_Mix) will have the a record related to it in MCorrections. On the form, since I can not have a subform on a continuous form, it's not really working out like i think it should, which makes me question my design. I'd like for my form to show all of the records, per SampleNo, and then I'd like to have the user input the MCorrections. I have tried using datasheet, but my form always changes back to the single for format.

Thank You

[FishVal]

Nice.

However it would be better to know exactly to which table FK belongs.
See the example I posted previously.

[csolomon]

table:MixDesign
DM_Mix, AutoNumber, PK
DM_SampleNo, Number
DM_Date, Date
jobNumber, Number, FK (Job Table, not listed)
panID, Number, FK (PanSize Table, not listed)
toneID, Number, FK (Tone Table, not listed)
finishDepID, Number, FK (FinishDep Table, not listed)
FinishActID, Number, FK (FinishAct Table, not listed)
DM_pan, Number
DM_SackMix, Number
DM_waterRatio, Number
DM_entAir, Number
DM_Memo, Memo
DM_DtNeeded, Dt
DM_Requestor, text

Table: MixSample
DM_Mix, Number, PK1
DM_MaterialNo, AutoNumber, PK2
matTypeID, Number, FK (MatType Table)
materialID, FK (Material Table)
matBatchWeight, Number
pigPercent, Number

Table: MatType
matTypeID, Number PK
matType, Text

Table: Material
materialID, Number, PK
matTypeID, Number, FK (MatType table)
material, Text
materialGrav, Number

Table: MCorrections
correctionsID, AutoNumber, PK
DM_Mix, DM_materialNo, Number, composite FK (MixSample table)
CorrDt, Date
corrDryGs, Number
corrWetGs, Number
corrAbsorption, Number

[FishVal]

Ok. Very well.
I'll look into it and will return to you in a couple of hours.

[csolomon]

OK

Thank you Kindly!

[FishVal]

Ok.

• Relation between [MixSample] and [Material].
  Considering sample data provided by you in post #3 I could say that MixSample.matTy peID field is definitely redundant. Really [MixSample] does not contain records with matTypeID/materialID combinations others than those in [Material] table. That means - [Material] table FK in [MixSample] table unambiguously determines materialID and matTypeID. The same you've already stated in words - [MatType] is in 1-to-many relationship with [Material]. However your current design presume many-to-many relationship between [MatType] and [Material].
• Relation between [MixSample] and [Material].
  There is no reason for MixSample.DM_Mi x being FK(MixDesign) to be also a part of composite primary key. [DM_MaterialNo] is an exellent candidate to PK.
• [MCorrections] table.
  As long as moisture tests are being performed for materials, results of these test should be stored in a table related to [Material] as many-to-one. These results should be then used to calculate corrections whatever you need for mixed sample.

It may be not clear so far for you how these relations could be used, well I will be glad to help you design appropriate queries, but that is how the data storage should be organized if I'm not missing something. Does it make sense?

Regards,
Fish

[csolomon]

Hi Fish,

I disagree with your first point: "I could say that MixSample.matTy peID field is definitely redundant. Really [MixSample] does not contain records with matTypeID/materialID combinations others than those in [Material] table. That means - [Material] table FK in [MixSample] table unambiguously determines materialID and matTypeID. "
>>I don't know if I agree that it is redundant because on my form I have a cascading combination box which first allows the user to select the MatType and then the second combo box is populated with the materials related to that mix type...the user then selects all of the matTypes and materials they will be using for the sample.
"However your current design presume many-to-many relationship between [MatType] and [Material]. "
>>Currently I have a one to many relationship between MatType and Material. Meaning there will be many materials of one Material Type

Your second point: Relation between [MixSample] and [Material].
There is no reason for MixSample.DM_Mi x being FK(MixDesign) to be also a part of composite primary key. [DM_MaterialNo] is an exellent candidate to PK.
>>I agree that I may be able to accomplish my goal in this table with out a composite key and just have the MixSample.DM_Mi x as a FK...since I already have that relationship set up as a composite key, what damage would it do if it stayed that way? It works to identify individual records in the table as well as tie that record to a specific MixDesign record.

Your third point: [MCorrections] table.
As long as moisture tests are being performed for materials, results of these test should be stored in a table related to [Material] as many-to-one. These results should be then used to calculate corrections whatever you need for mixed sample.
>>Moisture tests are performed for the matTypes that have been selected by the user for the mix samples, in the MixSample table, which is why I felt it needed to be connected to that table as opposed to the Material table because the material table is just a list of all available material to the user.

[FishVal]

...
>>Currently I have a one to many relationship between MatType and Material. Meaning there will be many materials of one Material Type

:D Beleive me or not, but your table design presume many-to-many relationship between MatType and Material regardless of what you are considering about it.

>>I agree that I may be able to accomplish my goal in this table with out a composite key and just have the MixSample.DM_Mi x as a FK...since I already have that relationship set up as a composite key, what damage would it do if it stayed that way? It works to identify individual records in the table as well as tie that record to a specific MixDesign record.

Composite primary keys are always troublesome. The one but not the only reason is that form control chould be bound to a single table field only, so having composite PK and, thereby, composite FK you will need to hold pair of controls and code a synchronizing logic.
Being on your place I would avoid this situation. But, obviously, I'm not on your place and your are certainly allowed to do what you consider to be suitable. ;)

>>Moisture tests are performed for the matTypes that have been selected by the user for the mix samples, in the MixSample table, which is why I felt it needed to be connected to that table as opposed to the Material table because the material table is just a list of all available material to the user.

Does it mean that record in [Material] table is basically an abstract material name? Is there a table that contains a list of real material batches held in stock and used to prepare samples?

[MMcCarthy]

OK the simple answer to the problems you are having with the MixSample table are the fact that you are missing a join table. Fish is right when he says that you have a many to many relationship. What he means is this:

If the tables were as follows:

table:MixDesign
DM_Mix, AutoNumber, PK
DM_SampleNo, Number
DM_Date, Date
etc.

Table: MixSample
DM_MaterialNo, AutoNumber, PK
matTypeID, Number, FK (MatType Table)
materialID, FK (Material Table)
matBatchWeight, Number
pigPercent, Number

Which is how they should be. Then you have a many to many relationship between these two tables. You cannot solve this by adding a composite primary key to the MixSample Table including the primary key of MixDesign. It just doesn't work.

What you need to do is create a join table to handle the many to many relationship as follows:

Table: DesignSample
DM_Mix, Number, PK
DM_MaterialNo, Number, PK

Now the only fields that you can include in this table are those that depend on BOTH of these keys. Those fields that only depend on DM_MaterialNo stay in the MixSample table.

Now I am not sure if the corrections table relates to MixSample alone or to both MixSample and MixDesign. If it is MixSample alone then the FK is simply the PK of MixSample. If it depends on both then briefly explain the relationship and I will help you incorporate it.

You should also check out this article on http://bytes.com/topic/access/insigh.../2#post2296372 to help you to understand the rules on creating tables and their relationships.