Strategy to map multiple filed in a single table t

问题:

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

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

回答1:

If you look at your first statement

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

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)

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

The benefits here are

• 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>