bosh bosh - 3 months ago 5
SQL Question

How to represent a set of entities from separate tables?

I have a few tables representing geographical entities (

CITIES
,
COUNTIES
,
ZIPCODES
,
STATES
,
COUNTRIES
, 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
CITIES
, 1 record from
COUNTIES
and 4 from
COUNTRIES
.

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:

(5,'CITIES',4)


(5,'CITIES',12)


(5,'ZIPCODES',91)


(5,'ZIPCODES',92)


(5,'COUNTRIES',15)




  • 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

(5,'4,12','','91,92','','15')



Any other ideas? Would love to hear your input.
Thanks!

Answer

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        g.id = c.geolocation_id
left join zipcodes z
on        g.id = z.geolocation_id
....

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

Comments