I have a database that will track "location" in a building.

  • Location1 contains country data.
  • Location2 contains building data.
  • Location3 contains room data.

Each location table has a FK to the parent table. So for example, I have data that looks like this:

 id   | name          | description
 1    | United States | 

 id   |  loc1_id   | name          | description
 1    |   1        |  buildingx    | building x in new york

 id   |  loc2_id   | name          | description
 1    |   1        |  5-125       | 5th Floor Room 125
 2    |   1        |  1-12b       | 1st Floor Room 12b

In my widgets table, i now want to specify where each widget is located. I don't really want to have 3 columns, one for each location table. I was thinking of saving values like this:

 id   |  loc_id   | name         
 1    |   1.1.1    | test widget   
 2    |   1.1.2    |  my favorite widget      

Does anyone have any comments on the advantages / disadvantages of this approach?

  • 1. the middle column is not a loc_ID, it's a combination of multiple location_ids. 2. Why don't you want three columns, there should be a clear distinction in your logic between what you want and what you want to succeed. 3. Use 3 columns, and separate the columns. 4. Going forward, and anyone who uses the system besides you won't get a headache trying to figure out what the heck is going on.
    – Hituptony
    Oct 8 '15 at 18:05
  • @Hituptony. I don't agree with your first point. It is a location. It's just combined. (but thanks for commenting) Point 2. I agree that it might be clearer to read. But i don't understand your comment about succeeding. Can you clarify?
    – dot
    Oct 8 '15 at 18:25
  • What three columns? If your widget refers to L3 (location within a building), then that location also contains the building (L2) and the building contains the country (L1). The loc_id field you show, or even three separate fields, allows you to enter something like 1.2.1, or any other combination that doesn't even exist in your data.
    – TommCatt
    Oct 12 '15 at 21:33

1.1.2 as a location seems very arbitrary. Your structure will not allow you to do much dynamic reporting and scaling up would cause issues. Every time you need to query up the chain, you will need to break the item up, which will break your index.

1.1.1 may refer to NY Fl 5, but 1.1.2 could refer to LA (los angeles, Louisiana,???) floor 4.

TBL1 - 1, USA TBL2 - 100, STATE, TBL1.FK TBL3 - 1000, CITY, TBL2.FK TBL4 - 10000, BUILDING, TBL3.FK, May want to also include ZIPCODE here. TBL5 - 100000, FLOOR, TBL4.FK

In you Data warehouse, you could flatten it, but you might want to keep separate Dimension tables for cube reporting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.