MySQL Design Question

[eluser]jedd[/eluser]
Agreed - tracking changes in the fourth dimension is an absolute pain in the proverbial.

Anyhoo, I'm starting to think that you should base your reviews on a location, and then use the location to look up the business(s) that are covered by that review. The model I'm getting in my head - hopefully much crapper than the model you've already come up with - for connecting all this data suggests that might be a slightly more robust way of doing it, even if it sounds a bit arse-about.

An interesting thought exercise, at the least. Consider these tables

review

location

location_review
(FK's into review.id and location.id - allowing for n:m)

business

business_location
(FK's into business.id and location.id - allow for 1:n, either in PHP (algorithm) or by specifying business_location.location_id as UNIQUE)


This is off the top of my head - but I'm curious what you reckon. How much data are you likely to be juggling here?