[Resolved] Database table question:

Mr_Mako

Hi all.

I have a rather involved question... so I apologize for then length of this post in advance.

I'm working on a site where players play a boardgame (a B-17 Bomber game) and then log their results as a "bomber group". The site is designed so that the users create accounts, which in turn creates an "airplane" with 10 crewmen. I've been working on this for about 2 weeks now, and thought I had everything set... until I got into the part of the page that displays "history"... ie., what bombers (and crewmen) have been "lost" while on missions.

The way I had it was a series of 2 tables: A "Crews" table, and a "Missions" table. The problem specifically was that as crewmembers (or aircraft) get "shot down", the records get "updated" in the "Crews" table when the user creates a new bomber / crew... thus overwriting the current crew for a given user.

So... I was thinking to attempt it this way: Creating a "Users" table (with player info like name, email, user id, etc.), a "Planes" table (which includes player id (linked), aircraft type, name, nose art link, crewmen, etc.), and the "Missions" table.

Then I started thinking about the crew themselves... since it's possible for a specific crewman to be replaced in the same airplane. I figured at that point I could create a "Crew" table that links (by id number) to the airplane in the "Planes" table (which is linked to a user by user id)... but that started to get too convoluted.

So... what I'm basically looking for is what the best way to accomplish this would be. I need to have a user that's established in the "Users" table... and that user associated with an airplane (which can change at any time) and the airplane with a crew (which can change at any time). Also, the plane itself has the possibility of being "lost" but the crew themselves not being lost... and thus being assigned to a new plane (which is still assigned to the same user).

I know the database needs to be designed with a "many to many" relationship base, but since this is the first time I've gotten into something this complex, I'm not exactly sure how to go about it. I can provide examples of my code and the current database I have if that's needed. I'm certainly not looking for anyone to "do this for me" or whatnot, just to provide short examples or ideas on how they might approach this problem.

Thanks...

nemonoman

You're definitely thinking on the right track. The problem you state is just what a relational database is for.

Entities as I see them:

user
mission
plane
crewman

I like to use singular names for tables: plane, not planeS. Old habit.

I'm calling one entity 'crewman'...individual records about individual people.

There's a nice top 1 to many relationship:

1 user has 1 to n missions, each mission has 1 to n planes, each plane has 1 to n crewmen.

You probably, however need some intermediate tables:

Each mission has MissionStatus: the status of planes involved at any given time, changing with changes in play.

Mission>MissionStatus>Planes
You'll actually need to create a table between them:
MissionGroupPlanes

Mission>MissionStatus>MissionStatusPlanes>Planes

Can you see how this would work?

You create a mission and the missionstatus record
missionid=222
missionstatusid=9292
status='startup'

As things change
missionid=222
missionstatusid=9293
status='takeoff'

etc

for each plane at each point in the mission status, a missionstatusplane record:

missionstatusid=9293
planeid=888

missionstatusid=9293
planeid=928

connecting to plane ids 888 and 928 respectively:

SELECT name
FROM plane
WHERE missionid=222
AND status='takeoff'
AND mission.statusid=missionstatusplane.missionsionstatusid
AND mission.id=missionstatus.missionid
AND missionstatus.planeid=plane.id

The first 2 wheres (222, 'takeoff') are the ones where you look for info.

The rest are how to join the tables together to prevent duplicate rows.

Similar for the crew

missionStatusPlaneCrewRevision
missionstatusid=929
planeid=888
Revisionid = 123

CrewRevisionCrewman
revisionid=123
crewman=1
crewmanstatus='alive'

which would relate to crewman records,

etc.

It's a pleasantly complex datastructure, with plenty of opportunities for establishing rules for creating and updating records.

Don't freakout that it seems complex at first glance. Most relational dbs appear that way, but once you get used to the concepts, it is SO much simpler to manage a many-tabled relational database than it is to try and cope with 'simpler' datastructures preferred by novices.

Mr_Mako

Much of what your saying makes sense... I really appreciate your reply.

In my case though, the process doesn't require data "throughout" the mission... it's only concerned with the "After Action Report"... or, the results, of the mission (the game is played out to completion, and then the results are emailed in rather than "playing" the game real time).

So I wouldn't need to keep tabs on the status of the planes and crewmen during the mission at all (but your point there is well taken, and filed away for future use... 🙂 )

A further question is how would I separate the individual crew men in the "Crewmen" table? As there are 10 crew members on each plane, I need to keep tabs on each one on each plane... and also (like the planes) keep a history of each crewmember for historical reference purposes.

As you say, I'm probably confusing myself on some of this... in thinking that it's much more difficult than it really is.

One thing also... the individual crewmen of each plane need to be "fixed". For example, if plane "A" (assigned to user "A") goes out, and comes back with "Pilot" and "Navigator" kia, I need to not only fill the "Pilot" and "Navigator" (only!) of plane "A", but keep the kia "Pilot" and "Navigator" in the db for reference. The crew positions inside the plane need to be kept separate.

Likewise, if plane "A" goes out, and then comes back and crash lands and is totaled, but the crew are ok, then Crew "A" would need to be re-assigned to plane "B"... while still keeping the relationship to user "A".

nemonoman

You might simplify somewhat if there are no intermediate status changes for missions and planes. You can probably see how to do this. I'll tell you that relational data modellers generally build a model that allows for such likely additional complexity (you'll want to do it someday, so build it now), but understand the need to 'collapse' tables as practicalities dictate.

As for the rest: You'd probably want to create

PlaneJobs:
which would specify the positions for each plane
1 Pilot
2 Copilot
3 Navigator
...
etc.

Then
MissionPlaneJobCrewman

Missionid=123
Plane=999
Job=1
Crewman=92929

See how that would work? More or less 10 records for each planemission detailing crew

Then you might add a disposition column to both MissionPlane:
dispostion 'shotdown'

and a endstatus to MissionPlaneJobCrewman
endstatus 'bought the farm'
or
endstatus 'lived to fight another day'

Newly Available Pilot Query:

SELECT crewman.*
FROM crewman, missionPlane, missionPlaneJobCrewman
WHERE missionplane.disposition='shotdown'
AND crewman.endstatus='lived to fight another day'
ANd PlaneJob.name='Pilot'
AND Missionid=123
AND MissionPlaneJobCrewman.Job=PlaneJob.id
AND MissionPlaneJobCrewman.Planeid=MissionPlane.Planeid
AND MissionPlane.missionid=MissionPlaneJobCrew.missionid

Mr_Mako

Thanks again for the reply. I'm sure that your suggestion will definately help me resolve the issue... I'll just have to play with it some.