MySQL/CakePHP DB Design Question

Question

Our real estate application has a table, Events, which has historically been linked to the Homes table via an Event.homes_id column.

Recently, for the first time, we added an event type which is not connected to a home, but a realtor. The question: is it good practice to now add a realtor_id column to the Events table? Something in me rebels at the idea of having two columns, home_id and realtor_id for every record, one of which will always be null for any given record. My boss says it's efficient and avoids the overhead of creating new tables. What are the rights and wrongs of this situation?

A corollary to the above question: part of our reluctance to create new tables is the fact that we're using CakePHP, and so it becomes harder to have absolute control over multiple linked tables via SQL joins. (Setting Cake's recursive property to maximum reduces the application's speed to a crawl.) Does, and should, working with Cake affect database design considerations? Or are we just working with Cake wrong?

Answer 1

My boss says it's efficient and avoids the overhead of creating new tables.

This strikes me as iffy. I think you need a different design.

Specifically, I would consider having Events owned by Homes and Realtors. By restructuring this you avoid the issue of one-of-two IDs being meaningful. I would represent Realtors/Homes to Events as has_many and the reverse as multiple belongs_to relationships if you actually need it.

Answer 2

Something in me rebels at the idea of having two columns, home_id and realtor_id for every record, one of which will always be null for any given record. My boss says it's efficient and avoids the overhead of creating new tables. What are the rights and wrongs of this situation?

Well, you're right, it probably is less efficient than optimal. However, adding another column (an INT, no less) that will be null 50% of the time isn't going to affect your overall database efficiency.

OTOH, it's going to take you a bit of effort to restructure your application. By just adding this column, you're effectively putting in a hack.

I think that's acceptable for this situation, although you may not like the aesthetics of it. Hey -- nobody likes hacks. It increases "technical debt". But google around for that term and you'll see that lots of people are saying to embrace technical debt, because it lets you continue moving forward, rather than trying to zero in on the perfect solution (which will escape you, despite best efforts).

It's a business decision -- is the aesthetic of your schema and codebase worth the cost (your hourly rate * # hours to "properly" fix it)? In this situation, I'd say it's probably not.

Answer 3

"we're using CakePHP, and so it becomes harder to have absolute control" - Why?

What do you actually lose by adding another column? Not a lot. Pride, maybe. All applications have compromises somewhere and this is a tiny one.

"(Setting Cake's recursive property to maximum reduces the application's speed to a crawl.)" - Then don't use recursive! Containable behaviour does a much better job and you can get only the data you want from however deep you need to go.

I would add another column, optimise with containable and move onto more important things.