涉及复合表的设计问题 [英] Design Question involving composite table

问题描述

您好：


我想知道我是否可以获得一些有关如何解决设计问题的建议

问题，涉及我的复合表。


我完成了项目的一部分。我将添加以下表格和

报告，捎带我现有的设计。

我已完成的部分允许我的用户创建设计

样本;这是由几种材料组成，以创建一个样本。我用这个设计完成了这个：


tblMixDesign：

DM_Mix（PK），DM_SampleNO，DM_Dt等


tblMaterial：

materialID（PK），matTypeID（FK），material，materialGrav


tblMixSample（复合表加入） tblMixdesign和tblMaterial）

DM_Mix（PK1），DM_MaterialNo（PK2），matTypeID（FK），materialID（FK），

matBatchWeight


Mixdesign和tblMaterial都有一对多的关系

tblMixSample。


我的问题与我相信我需要加入的另一个表有关

到复合表，tblMixSample（当然是它的父表

tblMixDesign）。在我看来，表tblMCorrections与

复合表相关。用户在
tblMixSample中选择的材料与在tblMCorrections表中与它们相关的信息

的材料相同。

我的问题是，当我尝试连接表时，访问权限不会允许我连接主键。是因为复合表中有两个

主键？


这是tblMCorrections：

correctionsID（PK） ，DM_Mix（FK），corr_Dt（日期），corr_wetGs，

corr_dryGs，corr_absorption


此表与其他（MixDesign和MixSample）有关，因为

当材料类型（matTypeID）为精细或粗糙（在

tblMixsample中选择）时，测量这些材料的水分。

用户将输入他们测量材料重量的湿度，

干燥，以及吸收（所有用户输入，存储在
$中） b $ b tblMCorrections）。正如我所说，我试图将MCorrections链接到

MixSample，以便继续使用材质类型（精细和

Coarse），但由于无法这样做，访问不允许我

连接密钥。如果我不能连接键，我怎么能说明这种关系呢？

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(PK), 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?

推荐答案

你好，所罗门。
@csolomon


首先关于现有表：

• tblMixSample.matTypeID是冗余字段，只要tblMaterial.matTypeID包含相同的信息。
• 为什么在tblMixSample中使用复合PK？

Hello, solomon.

@csolomon
First of all about the existing tables:

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

嗨鱼，


感谢您的回复。


"首先关于现有表：

tblMixSample.matTypeID是多余的字段只要tblMaterial.matTypeID包含相同的信息。

>> MatTypeID位于mixType表中，因为用户可以为一个或多个MixDesigns选择许多matType ...用户必须选择。

为什么在tblMixSample中使用复合PK？

>>使用复合键，因为我需要识别哪条记录与MixDesign（DM_MIx）中的记录匹配，以及只是为了拥有个人记录在MixSample中识别（通过PK2）。我不会使用PK2识别记录，但建议我使用复合键。


您想要我详细说明什么？正如我所说的，我需要创建一个描述这些业务规则的关系（我已经有了前两个）：


1）每个DesignMix都有很多材料./许多材料将是分配给一个或多个DesignMixes

2）每个DesignMix都有许多材料类型./许多材料类型将分配给一个或多个DesignMixes

3）每个混合样本（ tblMixSample）将有一个或多个水分校正（tblMCorrections）./每个水分校正将分配给一个或多个混合样本


这里是我的关系（1和2）的说明看起来像


DesignMix

DM_Mix / DM_sampleNo / DM_Dt / jobNumber

1/1984 / 12.15.2008

2/1985 / 12.01.2008

MatType

MatTypeID，MatType

1 /水泥

2 /精细骨料

3 /粗骨料


材料

materialID / matTypeID / material

1/1（水泥）/砾石1

2/1（水泥）/砾石2

3/2（精）/ sand1

4/2（精）/ sand2

5/3（粗）/粗1

MixSample- MixDesign和材料的复合

DM_MIx（PK1）/ DM_MaterialNo（PK2）/ matTypeID，materialID， matBatchWeight

1/1/1（水泥）/ 1（砾石1）/ 15

1/2/1（水泥）/ 2（砾石2）/ 2.03
1/25/2（精）/ 3（sand1）/ 25

2/33/2（精）/ 3（砾石1）/ 44

2/32/2（精）/ 4（砾石2）/ 45

2/22/3（粗）/ 5（粗1）/ 44

尽可能看，许多matTypes（水泥，精细，粗糙等）和许多材料（gravel1，sand1，gravel2，sand2，coarse1）可以分配给一个或多个DesignMixes


MCorrection是相关的到DesignMix，因为MCorrections表中会记录与所选的某些材料类型相关的信息，特别是精细和粗糙的matTypes。


我的问题是如何将它们与表？我是否需要另一个连接MixSample的复合表（混合样本实际上保存与DM_Mix相关的材料和材料类型）和MCorrections？

Hi Fish,

I appreciate your response.

"First of all about the existing tables:
tblMixSample.matTypeID is redundant field as long as tblMaterial.matTypeID contains the same information.
>>MatTypeID is in the mixType table because a user can select many matTypes for one or more MixDesigns...the 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 (tblMCorrections)./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?

你好，所罗门。

< a href =https://bytes.com/topic/access/answers/858275-design-question-involving-composite-table#post3444614 =nofollowclass =at_reply> @csolomon


这是否意味着mixType.MatTypeID与给定materialID值的tblMaterial.MatTypeID不同？

@csolomon


为什么[DM_Mix]和[DM_MaterialNO]是复合PK？

根据你的解释[DM_MaterialNO]有一个唯一的值。

所以你要么使用[DM_MaterialNO]作为PK，要么使用[DM_Mix]和[materialID]作为复合PK。

@csolomon


1）经典的多对多关系。你几乎有这个，只需要以适当的方式分配PK / FK字段。

2）那么，再次，材料类型与材料本身或给定设计组合中的材料有关吗？
3）这很有道理。进行什么样的水分测试？他们是否测量材料的水分含量或混合水分含量？


亲切的问候，

Fish。

Hello, solomon.

@csolomon
Does that mean mixType.MatTypeID is not the same as tblMaterial.MatTypeID for a given materialID value?

@csolomon
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.

@csolomon
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.

这篇关于涉及复合表的设计问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！