Strategy To Map Multiple Filed In A Single Table To A Single Field In Another Table

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 a

Solution 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 toObjecttable
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>