[SOLVED] Normalizing many-to-many relationships in database

[brownshoe]

I'm designing a database for a website for people who like to play with flight simulators and log their flights with an online system.  I'm trying to make a model with many pilots, many aircraft, many bank accounts, many airlines, etc.

 

This is my first time making a database, and my first web app.  I want to do it right.  So I'm learning about normalization rules.  My question is:  How can I make properly normalized tables when, for example, one pilot may have many ratings, and ratings will be held by many pilots. One pilot might also fly for several "airlines," and an airline should have more than one pilot.

 

I don't want to make fields in my tables that just contain large strings, like a "ratings" column on the "pilots" table that contains a list of that particular pilot's ratings.  In the case of "airlines," I similarly don't want to make a column with a list of that airline's pilots, nor do I want to make a list in the "pilots" table of the several airlines someone might fly for.

 

With a bunch of many-to-many relationships, what's the best way to avoid so much redundant information and keep the columns "atomic"?  Or is there a way to do what I want to do without the many-to-many relationships?

 

Thanks!

[DarkWater]

You use an intermediate table that contains keys from both tables to make one-to-many relationships on both sides.  It's better explained with an example:

 

pilots:

id, name, callsign

 

airlines:

id, name, abbrev

 

pilot_airline_assoc:

id, pilot_id, airline_id #id column is there just for good measure.

[brownshoe]

Okay, I think I'm getting this.

 

Originally, I was planning the table "FBOs" (they're like gas stations/parking lots for airplanes) to contain columns for small, medium, and large hangar spaces, and the prices charged by that FBO for each.  Instead of that, I'm going to make a separate table for "Hangars" with columns ID(PK), FBO (FK), location, size, and price.  Seems a little more efficient.

 

Now how about this one:  Each aircraft can have one owner, which could be either an individual or a corporation.  I would also like each aircraft to have a list of authorized pilots, who may or may not be the owner, and who will change from time to time.  Do I need to create an intermediate table called something like "pilot_authorizations" that will list each instance of a particular pilot being authorized to fly a particular aircraft, such as ID(PK), pilot(FK), aircraft(FK)?

 

Maybe I just answered my own question

[DarkWater]

Yup, you're starting to get it, lol.  Seems good.

[brownshoe]

Okay, still working on this...

 

An aircraft can be owned by either an individual or a corporation.  If an aircraft could only belong to an individual, then it would be easy to just make the "owner" column of the "aircraft" table a foreign key to an individual ID.  How can I do this, though, if the owner isn't necessarily just an individual?

 

I thought of making a separate "aircraft_owners" table, but the problem still remains:  I'd have columns like ownerID(PK, and FKed from the "aircraft" table), but then how would I associate the owner's identity with either an individual OR a corporation?

 

I also thought of making separate tables for privately-owned aircraft and corporate-owned aircraft, but I don't want to do this because an aircraft can be sold by a corporation to an individual or vice versa.

[DarkWater]

And the corporations aren't treated as individuals for purposes such as this?  Meaning they take up one row in the table and can therefore be manipulated as an individual.

[brownshoe]

Yeah, I think that's the way to do it.  Before, I had "pilots" being basically the same table as "users."  I think what I'll do is make a table for "users" with strictly account data.  Then I'll have another table for "entities," which could be either a pilot, a corporation, or something else.  Aircraft, and anything else with an owner, will be owned by an entity.  Each user will be able to be associated with one "pilot" entity.  This will allow for non-pilot users as well as putting individuals and corporations on equal standing in terms of ownership rights, and make it much easier to expand the system in the future.

 

Thanks!  I'll try to post a shot of the layout or a link to a .pdf or something when I get a good draft version of the architecture I'm working on...