Normalization Nightmare

[insub2]

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). The way the site works is that 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.

 

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.

 

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.

 

...Business <- BusinessLocations -> Locations <- ReviewLocations -> Reviews -> Business...
*wraps into a circle

 

 

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 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 doesn't seem as normalized as it ought be.

[emopoops]

i wouldnt use that kind of labling. i would just use simple php for ur equation. im sorry i dont get the question can u elbowate more?

[insub2]

i wouldnt use that kind of labling. i would just use simple php for ur equation. im sorry i dont get the question can u elbowate more?

 

PHP doesn't enter the scenario yet.  I'm trying to normalize my database design.

 

Could you be more specific about what is confusing you.  I feel I've described my situation thoroughly.

[emopoops]

use two tables.

[insub2]

use two tables.

 

But...

There are five tables.

3 main tables (Business, Locations, & Reviews)

And 2 describing the many-to-many relationships (BusinessLocations & ReviewLocations)

Reviews holds a BusinessID to define the many-to-one (Business-to-Review) relationship.

This is proving hard to normalize.

 

 

I'm beginning to think you haven't read my post, emopoops.

Not that I don't appreciate you help.

[fenway]

Why not link the review to the a UID in the many-to-many Business<=>Location.

[insub2]

Thanks Fenway.

 

I think I've settled on this:

Tables
----------------------------------------------
Business [ busID, *busData* ]
Locations [ locID, *locData* ]
BusinessLocations [ branchID, busID, locID ]

Review [ revID, busID, userID, *revData* ]
ReviewLocationss [ revID, branchID ]

Airline [ aLineID, busID, *aLineData* ]
Airport [ aPortID, locID, *aPortData* ]

 

It's pretty much what I had before with the addition of the BranchID to describe the Business Location Relationship.  This should make validating that Business J is actually at Location #27 more direct.

 

I left the busID in the Review table because, though it's not fully normalized, performing the joins required to get a busID for a Review would be a bit much for something that is used to lookup reveiws so much.  Plus, in the unlikely but possible event that a Review's BranchIDs point to multiple businesses, there will be a "master source" for making repairs.