Mysql – db table column design question

database-designMySQL

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: 

 Location1
 id   | name          | description
 ----------------------------------------------
 1    | United States | 

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

 Location3
 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: 

 widgets
 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?

Best Answer

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.

Related Question