0

Good afternoon. I am tasked at starting the framework (for a Final group project) at building, maintaining, and allocating a MySQL database for a proposed company. This company has many buildings, and may of them have multiple floors and many rooms. The database should store all the buildings, their GPS location, their number of floors, the floor plan images corresponding to that, all their rooms, and their locations within the building.

At the moment, I have a database with one table, "Buildings". In "Buildings" I have rows for the following: ID, Name, Latitude, Longitude, # of Floors, Floor plan Image(x4). I am assuming that there are a max of four floors. [probably a bad idea]

enter image description here

I am now stuck with how I want to store all the rooms, the floor plans, and the rooms location within the floor plan.

My initial thought was to create a new table for each building, and have rows for Room Number, Corresponding Floor Plan, and Location (Lat/Long). Yet if the company has, say, over 100 buildings, and each building has maybe only one floor and a couple rooms, then I think this would be overkill. Plus I think dealing with 100's of tables is bad practice.

Is there an easier way with dealing with lots of buildings, but with varying room amounts and floors?

Any suggestions would be much appreciated!

dovedevic
  • 673
  • 2
  • 14
  • 33

1 Answers1

3

Each entity should have its own table. The tables will be linked using Primary Keys and Foreign Keys. Number of floors should not be hard-coded, you should retrieve the number of floors using the COUNT function.

Normalize your database up to 3NF.

Here is what your DB should look like:

Buildings:
BuildingID(PK)
LongName
ShortName
Latitude
Longitude
etc

Floors:
FloorID(PK)
BuildingID(FK)
FloorName
etc

FloorPlans:
FloorPlanID(PK)
FloorID(FK)
FloorPlanName
FloorPlanImage

Rooms:
RoomID(PK)
FloorID(FK)
RoomNumber
Latitude
Longitude
RoomSize
etc
Eric S
  • 1,336
  • 15
  • 20
  • Just as a quick question, is there a preferred way/datatype to store the image? – dovedevic Apr 28 '16 at 00:05
  • Also, say I am just given a room number and a building, I can't seem to see how I would get out a floor plan... – dovedevic Apr 28 '16 at 02:51
  • @DoveDevic You shouldn't store whole images in a database, but if you have to, use BLOB. http://stackoverflow.com/questions/6472233/can-i-store-images-in-mysql – Eric S Apr 28 '16 at 13:29
  • @DoveDevic Look up `INNER JOIN` to get Floor plan. You join all the tables and use a `WHERE` clause. – Eric S Apr 28 '16 at 13:33