Is there a smart way to link a field to another field?

Question

Example: a database containing the names and properties of different metals (density, colour, melting point etc.) where I might have found the value for the density from one source (website, book, ...) and the melting point from another and I want to make it retrievable from which source any given value came.

The simple solution is to create a column 'Density' and a column 'Density Source' and so on for all properties but this seems like a very verbose way of solving this. Yes, 'Density Source' could be a simple integer with another table spelling out the source.

In a sense, I am looking to add something like a footnote to a field.

Wouldn't it be easier creating a table with [MaterialID, Property, Value, Source] and fill it like [mercury, 'meltingPoint', -39, "website here"? — jclozano, Dec 16 '11 at 18:19
There is no "the right way" for a database design. You should consider your use cases, data characteristics, etc. Which queries do you expect should work fast? How many data? — kan, Dec 16 '11 at 18:28
@jclozano - Although be careful, that's an EAV (Entity-Attribute-Value) design, which has some interesting (and usually frustrating) drawbacks. This might be the proper time to use it, though. Although, if there's usually a common source of data for a common set of attributes (density, atomic number, etc) those at least should be pulled out to their own table. — Clockwork-Muse, Dec 16 '11 at 22:29
@X-Zero Completely agree, has its drawbacks but when in need of flexibility it gets the job done :) — jclozano, Dec 16 '11 at 22:35
@jclozano - I just had my 'Duh' moment when I read your comment, of course this looks like the proper way of doing things. In a sense, when I said I wanted to link two fields, the answer is to create two tables and link them. I was still thinking from the output side when I should have been thinking from the input side. — sseelenluft, Dec 17 '11 at 02:54

score 0 · Accepted Answer · answered Dec 16 '11 at 18:22

The tough answer is that you're not going to get anywhere with anything less verbose than have a source table and then having a link back to each source for each bit of information.

What you can do is create the matrix of possible sources for your density, color, melting point, etc. and then assign a universal "SourceKey" for that, so that you would only have to store one source on the actual property table. Then, to get the density source, you'd do this:

select
    s.SourceName as DensitySource,
    m.*
from
    metals m
    inner join SourceMatrix sm on
        m.SourceKey = sm.SourceKey
    inner join Source s on
        sm.DensitySourceKey = s.SourceKey

That completely depends on how many combinations you can have and whether or not that's efficient at all, though. The database stores relations, but it doesn't store lineage.

I think your answer is essentially what jclozano suggested above in the comment to my question (see my comment to his comment). — sseelenluft, Dec 17 '11 at 02:49

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

If you want to flag the entire column, you could use SQL Server's extended properties or MySQL COMMENT options. These are "outside" of the relational design of your database

If you want to flag per column and per row, add another column, because it is relevant data to store in the model

edited May 23 '17 at 12:27

Community

1
1

answered Dec 16 '11 at 18:22

gbn

422,506
82
585
676

Is there a smart way to link a field to another field?

2 Answers2