将单个表中的多个字段映射到另一个表中的单个字段的策略 [英] Strategy to map multiple filed in a single table to a single field in another table

问题描述

我正在创建一个数据库，其中通过不同来源测量单个对象的不同属性.我有一个表，其中包含源列表以及源描述和源ID.我想创建一个表，该表将列出我将用于特定对象的特定度量的源.因此，在此表中，我将有一个用于objectID和将要测量的属性的字段.对于每个属性，我想映射一个sourceID.下图描述了我的设计

I am creating a database where different properties of a single object are measured via different sources. I have a table containing list of sources along with source description and source ID. I want to create a table which will list which source I am going to use for which particular measurement for a particular object. So in this table I will have a field for objectID and the propertys which I am going to measure. For each property I want to map a sourceID. The following diagram describes my design

我想知道在这种情况下将进行哪种映射? 在我看来，我将不得不将对象表中的每个单独的属性列映射到源表中的sourceID.有了这个，我将有多个1:n映射.这样好吗? 我正在使用MySQL工作台.请告诉我们是否可以采用更好的方法进行设计.

I would like to know what kind of mapping will I have in this case? It appears to me that I will have to map each individual property column in Object table to sourceID in source table. With this I will have multiple 1:n mappings. Is that alright? I am using MySQL workbench. Please tell if this can be designed in a better way.

EDIT#1 对于每个对象，我想查询哪个是特定属性"字段的来源.获取此信息后，我将使用给定的源将该特定属性的数据记录在另一个表中.

EDIT#1 For each object I would like to query which is the source for a particular "property" field. After getting this info, I will log data for that particular property in a different table using the given source.

推荐答案

如果您看第一条语句

"... <单个对象的属性是通过不同的来源进行测量的 ..."

"...properties of a single object are measured via different sources..."

您实际上可以立即看到，您可能正在寻找3张桌子.您建议的 Source 表看起来不错.我建议 Object 表虽然看起来更像

you can actually see straight away that you are probably looking for 3 tables. The Source table you propose looks fine. I suggest that Object table though look more like

ObjectId
ObjectName
ObjectDescription
... other object details (except measurement)

您的第三张表是您的 Measurement 表，可以想象是这样的

Your third table is your Measurement table, which could conceivably look like this

MeasurementId
ObjectId - reference to Object table
SourceId - reference to Source table
DatePerformed
MeasurementValue
Success 
Notes  etc

这里的好处是

• 您不需要在对象中为特定的来源添加特定的列.如果您突然有更多来源，将变得很难维护.
• 并非所有对象都需要每个源的值，尽管使用此结构，您仍然可以轻松确定某个对象是否缺少特定源的度量.
• 您可以为一个对象存储多个度量(通过DatePerformed分隔)，并使用Max(DatePerformed)可以检索最新的度量.

• That you don't need to have a specific column in your Object for a specific Source. This becomes very difficult to maintain if you suddenly have more sources.
• Not all Objects need a value for each Source, although with this structure you can still determine if an Object is missing Measurement from a particular source easily as well.
• You can have multiple measurements stored for an object (separated via the DatePerformed), and using Max(DatePerformed) you can retrieve the latest measurement.

然后，您可以获取结果列表

Then you can get a list of results, if you then do

SELECT ObjectId, SourceId, DatePerformed, MeasurementValue
FROM Measurement
WHERE ObjectId = <your Object>
[AND/OR] SourceId = <your source>

这篇关于将单个表中的多个字段映射到另一个表中的单个字段的策略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！