How to design a table that stores name data that can repeat in the real world?

Question

I'm looking for direction on a design quandry that's challenging our database designers. I have searched the archives and Google and yielded nothing conclusive, though I suspect this is a simple/novice type of issue.

We have a table that stores event locations. It is the called the Location table. The table requires that the LocationCode be unique. The table also requires that the LocationName be unique. However, there are times in the real world where two locations will have the same name though they exist in different places (e.g. two schools with the same name that exist in different counties in the same state). One designer insists the names should be forced to be different by appending a value to the name like the county name (e.g. Union High School - Francis County). Her rationale for this is about ensuring "controlled vocabulary". Another designer insists the names should be allowed to be the same because this reflects reality and LocationCodes should be used to enforce/track uniqueness.

I'm leaning in the direction of the second designer -- the names should be allowed to be different. A comparable example that I can think of is the fact that people often share the same name (e.g. Jane Doe). In a table that stores contact information about people, it would seem the names wouldn't be forced to be different and a social security code would do the job of tracking uniqueness.

So are there general guidelines or standards for this issue? A link to helpful documentation would be fantastic. Thanks in advance.

Answer 1

I'm leaning in the direction of the second designer -- the names should be allowed to be different.

I think you meant to say that you think the names should be allowed to be the same, that your table should allow duplicate names.

In a table that stores contact information about people, it would seem the names wouldn't be forced to be different and a social security code would do the job of tracking uniqueness.

While that's true in a simple case, in real-world applications you find that

• some foreigners living in the USA don't have SSANs,
• SSANs have duplicates, both legitimate and illegitimate, and
• some US citizens don't have SSANs. (Usually due to being born somewhere besides a hospital.)

Unambiguously identifying people is both a hard problem and an application-dependent problem. The local Bureau of Motor Vehicles has its ways of identifying people; the IRS has its ways; employers have their own ways; schools and hospitals have their own ways (privacy laws have a big impact here). You will probably need to find your own way.

Here's what you simply can't avoid: at some point, somebody at your company has to be able to tell which of many database rows that say "John Smith" match up with the angry customer in front of your desk.

I look at location names like this. The full name of "San Francisco" is "San Francisco, Calif., USA". The full name makes it easy to distinguish "Clinton, Miss." from "Clinton, Iowa".

I have found cases where two distinct places have the same informal name. For example, there might be a town named "Natural Bridge, Tenn.", and another place in Tennessee that is a natural bridge also named "Natural Bridge, Tenn." The difference is that one of those places is invariably a city, and the other invariably isn't. (In my experience, at least. I wouldn't be surprised to discover exceptions.)

Whether any of those real-world issues matter to you, though, is application-dependent. I don't have to deal with place names that aren't cities, so storing the "full name" is a simple and direct way to insure good data.

In your case, for event locations, I can't see allowing duplicate names being practical in a business sense. If the names are duplicated, you essentially require people to memorize which location code refers to each real-world location, given its name as a hint. With a database of any significant size, people can't do that reliably without writing down the differences anyway.