many to many setup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Radnick - 23 Jan 2006 18:41 GMT

I have two tables plus a junction table

Table1=Aircraft
aircraftid (pk)
year mfd
location2003 (what location aircraftid was based out of in 2003)
location2004
location2005

Table2=Location
locationid (pk)
#aircraft2003 (this is the # of aircraft at locationid in 2003)
#aircraft2004
#aircraft2005

Table3=AircraftLocation
aircraftid (pk)
locationid (pk)

I want to query a specific aircraftid and know how many aircraft were
at the location where aircraftid was based in each year (2003, 2004,
2005). The way I have it set up does not work since aircraftid is the
pk and not location2003 (for example). Suggestions on how to
restructure the tables? P.S. The data I have been provided are in the
form that Table1 and Table2 are in.

Thanks very much. Bill

Reply to this Message

jeremygetsmail@gmail.com - 23 Jan 2006 18:48 GMT

Bill,

Create a "YearParked" field (that's a horrible name, but you shouldn't
call it just "Year") in Table3. Then the last three fields in both
Table1 and Table2 will be unnecessary, which is good, because as you've
got it it's too easy to have inconistent data.

Once you do that, you'll have more records in Table3. And you'll be
able to build your query pretty easily.

For a great write-up of *why* my strategy is the one most database
folks would use, check out Paul Litwin's excellent article "The
Fundamentals of Relational Database Design". You can get it on my old
website, http://www.abcdataworks.com.

Jeremy

Reply to this Message

Bill Radnick - 23 Jan 2006 22:04 GMT

Thanks for your help.

Bill

Reply to this Message