Reputation: 143
So I have searched around but haven't found a satisfactory answer.
I have different types of locations, as stated in the title. Given a type of location (i.e. city), the less granular locations can be inferred. I.e. if you know you're in Oregon, it implies you're in the United States, which implies you're in North America.
We have Objects that reference locations, but the granularity is not all the same. Some items might point to neighborhoods, others are only known down to the city level, while some are only known to a region, etc.
There were two ways in which I thought of organizing the data, this is the way I am leaning towards:
You can then have the object reference the location off its primary key, and then other locations can be inferred. Does this make sense or is there a better way I could be organizing the data?
If I were to slip other location types in later, for example counties in between Cities and Regions, might this present a problem? I'm thinking it would be no more a problem than with separate tables, but perhaps there's a better way I can keep track of things in a logical way.
Upvotes: 2
Views: 928
Reputation: 18940
This is a case of subclasses, often called subtypes. It's complicated by the fact that some subtypes are contained in other subtypes. The container issue is well handled by classical elementary relational database design.
The subclass issue requires a little explanation. What OOP calls "subclasses" goes by the name "ER Specialization" in ER modeling circles. This tells you how to diagram subclasses, but it doesn't tell you how to implement them.
It's worth mentioning two techniques for implementing subclasses in SQL tables. The first goes by the name "single Table Inheritance". The second goes by the name "Class Table Inheritance". In class table inheritance, you will have one generic table for "locations" with all the attributes that are common to all locations, regardless of type. In the "Cities" table you will have attributes that pertain to cities, but not to countries, etc. You will have other subclass tables for the other types of locations.
If you go this route, you should look up another technique, called "Shared Priomary Key". In this technique, the id field of the subclass tables all contain copies of the id field from the superclass table. This requires a little effort, but it's well worth it.
Shared primary key offers several advantages. It enforces the one-to-one nature of a subclass relationship. It makes joining specialized data with generalized data simple, easy, and fast. It keeps track of which items belong in which subclass, without an extra field.
In your case, there is yet another advantage. Other tables that reference a location by using a foreign key don't have to decide whether to reference the superclass table or the subclass table. A single foreign key that references the superclass table will also implicitly reference one of the subclass tables, although it isn't obvious which one.
This isn't perfect, but it's very, very good. Been there, done that.
For more information, you can google the techniques, or find relevant tags here in SO.
Upvotes: 1
Reputation: 48267
Good question.
You should definitely go with your first option. If you look at any data modeling patterns book, they all choose that way.
Is this North America only, or global?
Issues:
Cities/Towns/Hamlets/Villages are children of Divisions (generic term for state/province), though not in, say, England, where they are children of Country (or is it County)
Postal Areas (postal codes, zip codes) are children of Divisions too, not county or city. Some cities reside entirely in zips, and some zips reside entirely in cities
Counties are children of Division too. Manhattan contains counties, whereas most counties contain cities.
I would read Hay's Enterprise Model Patterns if you are hoping for a global solution. It's on safari for cheap.
Upvotes: 0
Reputation: 79969
What about:
Countries:
Id
,Name
.Regions:
Id
,CityId
,Name
.Cities:
Id
,RegionId
,Name
.Neighborhoods:
Id
,CityId
,Name
.This for location types. But the main problem in your case is
but the granularity is not all the same.
For this:
Object:
Id
,Name
,LocationId
,Type
.Upvotes: 0