MySQL Design Question

[eluser]insub2[/eluser]
My solution doesn't seem right to me....

I'm working on a reviews website that focuses on travel so I have to deal with Airports and Airlines (among other things). So I have a table for Businesses and a table for Locations that are related to each other by a BusinessLocations table. And a table for Reviews that contain one Business ID and are also related to Locations by a ReviewLocations table.

Code:

...Business <- BusinessLocations -> Locations <- ReviewLocations -> Reviews -> Business...
[i]*wraps into a circle[/i]

My issue is that with this design, it's possible for a Review to be of Business at a Location that isn't a Location for that Business.

...er, say there is a BusinessLocation relationship between United Airlines and O'Hare Airport. And there is a Review of United Airlines but something got messed up in the database and the ReviewLocation says the Review is for United Airlines at 123 Fake St.

There is an implied relationship between the Location and Business through the Review but the relationship is independent of Reviews. So I need to repeat data. But that makes me feel like I'm doing something wrong.

[eluser]jedd[/eluser]
[quote author="insub2" date="1261154594"]
But that makes me feel like I'm doing something wrong.
[/quote]

Correct.

If you were to display your information using .. well, less words - it would look like this:
Business (n) -- to -- (m) Location
Review (n) -- to -- (m) Business

This is sufficient information to do what you are wanting to do. That is - you need two relationship tables, and three entity tables.

Unless the situation you describe - a review applying to a business at a location other than the business's location - is something you need to be able to model? This seems unlikely, though.

There is an implied relationship between the Location and Business through the Review but the relationship is independent of Reviews.

When describing these kinds of data you need to be really clear about the distinction between intent or desired behaviour, and apparent behaviour - that divergence is the key to understanding the problem.

So, yes, there is an explicit relationship between Location and Business. Any additional way of storing that relationship would de-normalise your schema.

Just to confuse matters .. a business may have multiple premises. A review may apply to one branch of a business, specific to a location. Your design may need to accommodate this.

[eluser]insub2[/eluser]
[quote author="jedd" date="1261156517"]
Just to confuse matters .. a business may have multiple premises. A review may apply to one branch of a business, specific to a location. Your design may need to accommodate this.[/quote]

Yep, confused!

The way the site works is thtat one can only write a review for one business at a time. And you don't have to review all of that business' locations (branches). BUT each location can have multiple businesses.

To expand on the example:

United Airlines will fly out of O'Hare, Atlanta, LAX, etc.
(Business to multiple locations)

If someone wanted to review a flight they took from O'Hare to LAX, I can't just do Review->Business (United) because it wouldn't have the Locations (Airports) specified. And I can't just to do Review->Locations because O'Hare and LAX have multiple Airlines flying out of them (thus, no business would be specified).

So, I need to specify Review to Business AND Review to Locations. Hence my trouble.

[eluser]jedd[/eluser]
Can you ever review an entire business - ie. one that has multiple locations, and you want to review them all as a single entity?

Can you ever review a subset of an entire business. ie. one that has multiple locations, and you want to review >1 & <n of those locations?

Can you ever review a business without reference to any of the locations it has?

Do any businesses have no location (eg. an Internet-only business)?

[eluser]insub2[/eluser]
[quote author="jedd" date="1261171995"]Can you ever review an entire business - ie. one that has multiple locations, and you want to review them all as a single entity?[/quote]
You'd review and entire business by not specifying any locations (so yes to #3).

Can you ever review a subset of an entire business. ie. one that has multiple locations, and you want to review >1 & <n of those locations?

The Location IDs will be in an arbitrary order because of how the Airport data is imported -- Locatoins 50 through 55 may not refer to same business. The user will essentially check off the locations they are reviewing.

Can you ever review a business without reference to any of the locations it has?

Yes.

Do any businesses have no location (eg. an Internet-only business)?

As of right now, no. The site does not offer reviews on things without Locations. But theretically, I suppose it should be possible because the only required relationship is between busines and review.

[eluser]jedd[/eluser]
If I review Business J's branch located at Location #27 - and a year later Business J closes the office at Location #27 - what do you want to happen to that review?

[eluser]insub2[/eluser]
[quote author="jedd" date="1261194487"]If I review Business J's branch located at Location #27 - and a year later Business J closes the office at Location #27 - what do you want to happen to that review?[/quote]

The location is marked closed and can no longer be review. Existing reviews are not effected.

The reviews get tallied for teh business as a hole unless the user drills down to just the locations (via a list presented on/accessible from the Business' page).

[eluser]jedd[/eluser]
If Business J's branch at Location #27 relocates (with all the same staff move with it, of course) say from 123 Fake St to 124 Fake St - does it get a new location code?

[eluser]insub2[/eluser]
It shouldn't but, yeah, it will get a new location code because I'm not getting paid enough to make a system that will keep track of that.

[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?