2

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...

2
  • Sane design, assuming you're not storing the actual HTML and the app will figure it out from the DB.
    – Philᵀᴹ
    May 4, 2014 at 0:15
  • Thanks, and yes, there would be no markup language stored in the database tables; the PHP file retrieving these results would issue an echo statement concatenating the necessary markup around the data to serve to client.
    – Justin
    May 4, 2014 at 0:25

2 Answers 2

1

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.

3
  • Thanks :) I feel bad that I can't +1 your answer due to insufficient rep. Regarding the image names and your concerns...What do you think about this idea for image filenaming scheme: "(BLDG_ID)-(IMG_ID).jpg" Where BLDG_ID and IMG_ID are both auto-increment. Does that seem like a good way to do it? Then the only thing for the PHP code to know is which directory to look in and then find an the image that matches those variables.
    – Justin
    May 4, 2014 at 19:29
  • 1
    For example an image could be named like this: "3-13.jpg" and then PHP code would get "3" and "13" through a db query and do something like echo "<img src="$theQueriedBLDG_ID" . "-" . "$theQueriedIMG_ID" . ".jpg" />
    – Justin
    May 4, 2014 at 19:36
  • @Justin - That would be OK. The names will not become so long that are unwieldy. The primary key of your building and unit rows will (should!) never change once written you you will have no problems with linking an image to the correct property. Definitely keep the server & path out of the image name, though. May 6, 2014 at 3:47
0

There is some redundancy in your design.

If you think of a condo building and condo unit as different types of facilities, then you can put them in one table, like this:

create table facilities (
  facility_id int primary key,
  type char(1) not null, --B=Building, U=Unit
  building_id int null references facilities(facility_id) check ( case type when 'B' then null else not null end ),
  [common fields like address],
  ...
);

This is the pattern used in Enterprise Model Patterns, by Hay.

And you can store all pictures in one table, and link to facilities with a junction table:

create table pictures (
  picture_id int primary key,
  ...
);

create table facility_pictures (
  facility_id int references facilities(facility_id),
  picture_id int references pictures(picture_id),
  primary key (facility_id, picture_id)
);

Storing images in the file system is considered an anti-pattern by some (like Bill Karwin, and me). If you use good web server caching and incremental database backups, then it's fast, and you get the benefits of relational integrity, and won't forget to backup your images.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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