Problem Statement: The Name of the Primary Contact field in the Company table could be a simple field or it could reference a record in the People table, using a Link to single field from another table field.
When is the former better than the latter?
If there are multiple references to the same piece of information, it is easier to maintain it one place, especially if that information is likely to change. You might create a Link to Multiple Fields from Single Table reference to pull in the person's Name, Telephone Number and Email address from the Contact table. If the Telephone number changed in the master record in the Contact table, that change would automatically be reflected in all the records that reference it.
It is easier to keep the data consistent. When users enter free-form text, there are likely to be minor variations in spelling, capitalization, etc.
If the information has multiple fields that are always linked, is accessed frequently, or is likely to change and must be kept up to date across several tables, then store it in its own table and create a reference to it. If the information is just being used in one place, then create an independent field.
A good question to ask is "If the information is stored in an independent field, how many times are we going to re-enter it?" For instance, a user's contact info stays constant and is needed for every ticket they submit, so it would be efficient to make this a reference to their record on the Contact table. But the name of a CEO of a Company could reasonably be held as an independent field in the Company table.
Related articles |