CODEWITHSUNDEEP
mysql
Salim Ibrohimi
Salim Ibrohimi

Reputation: 1391

Which scheme is best for storing country, region and city data

I need to store information about countries, regions and cities. Which schema should I use:

Tables:

  1. country (id, code, name);
  2. region (id, ctry_id, code, name);
  3. city (id, rgn_id, code, name).
| id | code | name |
| 1  | US   | USA  |

| id | ctry_id | code | name  |
| 1  | 1       | TX   | Texas |

| id | rgn_id | code | name   |
| 1  | 1      | DS   | Dallas |

OR

  1. location (id, pid, type, name);
| id | pid | type | name   |
| 1  | 0   | ctry | USA    |
| 2  | 1   | rgn  | Texas  |
| 3  | 2   | cty  | Dallas |

Which approach is better?

Upvotes: 2

Views: 666

Answers (2)

Dan Conn
Dan Conn

Reputation: 59

So it depends what you're going for really.

I would guess that option 2 may be more performant in speed terms, but at the cost of the table ending up quite confusing with lots of entries. I would recommend perhaps restricting the type field to a list of known types and only altering when you need to with this approach. Also consider how this would feel to someone with little knowledge of the data needing to maintain this.

The first option may not be as fast, but will be more structured, and with tables that are appropriately named. This would probably be the way that you're colleagues unfamiliar with the data would thank you for.

I would give option 1 a go, and if it meets your performance needs then go with that one. If it's too slow for you try the second.

Personally, I prefer the first. Sometimes a slight hit in performance is worth readability. Other times not.

Good luck in your choice.

Upvotes: 2

Femi Oni
Femi Oni

Reputation: 824

I would say schema 1 is relational and that is what mysql expect and i think would perform better. but if you would ever need to work with noSqls like couchdb, schema two is how it works and it good for queries but not for generating reports.

Upvotes: 0

Related Questions