bosh bosh - 1 year ago 33
SQL Question

How to represent a set of entities from separate tables?

I have a few tables representing geographical entities (

, etc).

I need to way represent sets of geographical entities. A set can contain records from more than one table. For example, one set may contain 3 records from
, 1 record from
and 4 from

Here are two possible solutions:

  • A table which contains three columns - one record for each entity. The table will contain multiple records for each set, all sharing the the set number.

set_id INT, foreign_table VARTEXT(255), foreign_id INT

Sample entries for set #5:






  • A table which contains a TEXT column for each entity type, which will include a string set with the appropriate entries:

set_id INT,cities TEXT,counties TEXT,zipcodes TEXT,states TEXT,countries TEXT

So the above set will be represented with a single record


Any other ideas? Would love to hear your input.

Answer Source

Both solutions you propose don't have real foreign keys. In the first solution, one foreign_id can point to many tables, which is hard (or at least inefficient) for a database to enforce. The second solution stores multiple values in one column, which is the one thing everyone agrees you shouldn't do (it breaks first normal form.)

What I would do is this: cities, zip codes, and states all "have a" geographical location. The normal way to implement that is a one to many relation. Create a geolocation table, and add a geolocation_id column to the cities, zip code, and state tables.

EDIT: Per your comment, to get from a geolocation to its cities:

select    *
from      geolocation g
left join cities c
on = c.geolocation_id
left join zipcodes z
on = z.geolocation_id

The database will resolve the joins using the foreign key index, which is very fast.