Mysql – General structure advice for beginner

database-designMySQL

I am starting my first DB-Driven website project for a mock real estate agency and would like a bit of feedback on my general approach to how I will drive the site with the DB.

Specifically I'm worried about two things:

  1. If my primary and foreign keys are correct/logical
  2. If my approach to using image resources in conjunction with the DB is a good approach (See comments beside the tables in code below.)

A bit about the site:

Generally, the site structure will be categorized into Condo Building, and each Condo Building has a 1..* relationship with Condo Units. A user can browse by building and be presented a building overview when selecting a particular Condo Building, and a list of all Condo Units in that building which this agency is selling. There will be pictures for Condo Buildings and pictures for Condo Units and my job naturally is to chain them together with relationships.

Regarding list item #2:

I have done some googling and it seems the general consensus is that although MySQL supports storing of BLOB types, it is considered bad practice as it taxes the database. So my approach is to have an image directory for Condo Buildings and an image directory for Condo Units, and each image filename therein will be the primary key ID so that I have a way to hook into the images in my PHP

--------------------
| CondoBuildings   |
--------------------
| ID (Primary)     |  // Auto increment int
| //fields         |  // only that which pertains to the building and NOT the units
--------------------

--------------------
| CondoUnits       |
--------------------
| BLDG_ID (Foreign)|  // CondoBuildings Primary Key (ID)
| ID (Primary)     |  // auto increment int
| //fields         |  // only that which pertains to an individual unit
--------------------

--------------------
| UnitImages       |
--------------------
| UNIT_ID (Foreign)|  // CondoUnits Primary Key (ID)
| IMG_ID (Primary) |  // <img src="/images/units/$refToID.jpg"  
| str AltVal       |  // alt="$refToAltVal"
| int Height       |  // height="$refToHeight"
| int Width        |  // width="$refToWidth" />
--------------------

--------------------
| BldgImages       |
--------------------
| BLDG_ID (Foreign)|  //CondoBuildings Primary Key (ID)
| IMG_ID (Primary) |  //<img src="/images/units/$refToID.jpg"
| str AltVal       |  // alt="$refToAltVal"
| int Height       |  // height="$refToHeight"
| int Width        |  // width="$refToWidth" />
--------------------

So basically this is my plan, but I need to be sure that this is a logical approach or if there's some substantial drawbacks to this approach that ppl with more experience could point out.

Ensuring images are properly named would be a concern perhaps…

Best Answer

Your design is sound.

I have a concern about using the image filename as the key. Although academically acceptable there could be performance implications. A long string value like that is not going to make for an efficient index i.e. one with lots of IDs per page.

Secondly, there's nothing certain but change. At some point you will have to re-locate these files onto a different directory/ server/ domain. At that point you will be updating the primary key of a table which, while do-able, is rarely pretty.

I understand the example you've shown only has four tables. I would anticipate you will have more before your system goes live and then these concerns will become more significant. I'd use a surrogate key instead.

Related Question