| Posted | Message |
|---|
EDanaII
7/18/2010 9:17:03 AM | The other day, Don pointed out a problem with the DB to me. This problem has actually been on my mind for some time, but fixing it represents, potentially, a lot of work. How much, I can't say, unless I actually "quantify" that work. To that end, I'm starting some initial analysis, and for that, I need the help of other minds on this.
Here's the problem: the database represents crews and the missions they flew, but this representation is a static snapshot of that crew at a given point in time, likely when they were first assigned to each other. An example of this is R.H. Kaufman's crew: http://www.401bg.org/history/crew.asp?cid=12271 , please look at the crew notes, where it mentions Crew #2: http://www.401bg.org/history/crew.asp?cid=12272 . This is because Kaufman lost a large portion of his crew on a fateful mission, and the second listing represent the change in crew.
This, however, is a dramatic representation of the problem. This problem actually occurred frequently with crews as various members were killed, captured or were reassigned. You see, events of the war changed the make up of each crew over time as the war progressed.
Resolving this issue requires introducing what's called an "Event" model into the database. What it would do is record events as they occurred to the organization, crews, groups, personnel and aircraft over the course of the war. For example, it would record the event that necessitated the creation of Kaufman's second crew and the subsequent change in crew. It would also record what happened to each member of the first crew (Killed, Missing, Captured, etc...) as well as, the events that affected the second crew until they finished their service. (Finishing their service as a crew is another example of an event that could be recorded.)
Here's why I'm opening this for all to consider: I need to identify all the "entities" involved in the 401st so that they may be associated with "events." At present, the entities I believe will need to be recorded: Persons, Combat Crews, Support Crews, Squadrons, the 401st and Aircraft.
Does anyone have any other entities that should be added to this list? Are there any other groups that need to be considered?
Please feel free to post any and all thoughts here. Do not be shy. The more ideas you provide, the more complete model I can create.
|
donaldbyers
7/18/2010 11:06:59 AM | Unless the structure is in one of the other events you mentioned I would think Mission would be the other which denotes the exact mission which things are different. You can tell me if I am wrong I can take it hahaha!
I also know the potential of lot's of work, but there was a lot of input when the database was first construted and then came the data entry.
Don
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|
EDanaII
7/18/2010 1:17:41 PM | And being killed in action, missing or completing a tour are events too, instead of being just status codes in the current database. 🙂 But I don't want to focus on Events right now; we will get to those later. My current purpose is to identify all (or as many as I can) Entities that we will record events for. As already stated, such entities are: Personnel, Groups (Combat Crews, Ground Crews), Squadrons (composed of Crews), the 401st itself, maybe the 8th Air Force, and so on...
So, once again, does anyone have any more to add to that list? Does anyone have any additional thoughts on this subject? Some other possible (but less relevant) Entities might be Deenethorpe, or Weldon, but I doubt that they'll need to be recorded directly. For example, Deenethorpe and Weldon need only be mentioned when the "Crashed on Take Off" event is recorded for the Zenobia Al Elephanta.
Paul? Are you reading this? You've become our resident 401st expert, do you have any thoughts there? Dale? How about you? Anyone else? I just want to hear your thoughts so that I can weigh this issue. 🙂
Ed.
|
donaldbyers
7/18/2010 2:51:39 PM | Speaking of changing position you have pilots moved to staff positions, to squadron leaders and so forth.
Don
Am sure that Paul will have something to say when he reads this Topic.....
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|
EDanaII
7/18/2010 10:10:53 PM | Yep, those would be "Events" too, but the question arises, do we want to track them? While I've been warning about the potential workload this might create, I'm more worried about the strain this could place on our Access database. It has the potential to push it to its limits. But that's why I'm doing some analysis here. The only way to find that is to explore the options a little bit.
Ed.
|
donaldbyers
7/19/2010 7:57:08 PM | I do agree with you and think we should only follow them as far as crew members.
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|
Paul Bellamy
7/28/2010 3:16:50 PM | I'll have a think and see what suggestions I can come up with.
TTFN,
Paul
Paul Bellamy
|
EDanaII
7/29/2010 7:16:32 AM | No worries, Paul. At this point, this is nothing more than a brainstorming session, so... if ya can't think of anything, it's possible we've thought of it all. 😉 I'm also taking this slow, considering the potential workload it represents.
|
donaldbyers
7/29/2010 2:00:26 PM | There are also instances where a pilot and or his crew flew with another squadron an that being flying as lead for that squadron. This may already be a function when filling out the information on crew members and pilots.
Don
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|
Paul Bellamy
8/3/2010 5:37:24 AM | While struggling to decipher the out-of-focus squiggles of the Gushing Gertie wall panel crew roster all I can pull out at the moment are a few of the ranks.
Of course, one of the static entries in the database are personnel ranks, which changed over time. Promotions and transferrs are a couple of the things that should be recorded in the Squadron Dailies, so in theory that's something that could be incorporated at some point.
As an aside, is there any way to make the search function a bit less rigid?
Many times I'm stuck with either having a few letters of a name, or even a number of spellings, which means I have to plough through the entire assigned personnel list with Firefox's own search function to get anything at all.
TTFN,
Paul
Paul Bellamy
|
donaldbyers
8/3/2010 8:22:26 AM | You can use the "%" sign in your search just a you could use the "*" Paul *mond or %mond.
Don
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|
EDanaII
8/3/2010 9:23:56 PM | Yep, Don's correct. You can pattern match any name simply by inserting a % sign. So, if you not sure if the person is a Johnson or a Johnsen, you can enter Johns%n, or even John%. Or if you're sure he's a son, but not of what, you can specify %son. Nor are you limited to one %. Place as many as you need strategically in the persons name. That said, the use of too many % could cause things to slow down, so use them wisely. 🙂
That said, I'll try and put a quick Event model together over the weekend, to push the discussion a little further.
|
EDanaII
9/14/2010 9:01:11 PM | Returning to this problem for a moment, I find that the database does appear to have the capability of holding crew information spanned over time. The key is in its relationship to Missions. So, the issue becomes how do we manage this information. I'm thinking some web pages can be built that will allow us to maintain all the crewman for each Pilot on a per Mission basis. My next step will be to prototype the functions.
The above image, BTW, is an ERD (Entity Relation Diagram). It simply tells us how each Table (think a collection of records) relates to other tables in the system. It shows me that the Crew data is/can be tied to each Mission, which means a new Crew record can be created for each Mission where the actual Crew Members changed. This is potential confusing though, if you don't grasp the concept right away, which is why I'll prototype the function and allow Don (and Rick if he wishes) to play with it.
More later as I progress.
|
EDanaII
10/3/2010 6:24:46 PM | Returning to this issue, I had a chance to work on it while I was up in Heber. As I worked on it, I noticed a couple of problems that need to be resolved and am posting here to discuss the issues.
First issue I'm concerned with is that, at present, the database limits the number of crews to 10. This is inherent in the nature of how a crew is identified and can be changed, but I'd first like to determine if a ten crew limit is realistic or not.
So, first question: Did anyone ever manage more than ten crews? I'm going to make the assumption that they didn't, but this needs to be discussed.
The answer to this issue will then determine my next issue. The method the database uses that limits a given serviceman to no more than ten crews is also used to uniquely identify that crew. If you go to the Crews page and search the page for this text: "(#2)" you will see what I mean. For the Kaufman Crew(s), you see:
- Capt R.H. Kaufman
- Capt R.H. Kaufman (#2)
And for the Lozinski Crews you see this:
- 2nd Lt S.J. Lozinski
- Capt S.J. Lozinski (#2)
If the a given airman was in charge of more than 10 crews, then this becomes a problem for me to manage. More than 10 would effectively break portions of the site.
On a side note, I personally think that the crews should be identified using the times that the crew served. For example, the first Kaufman crew was in existence for only for a couple weeks and, perhaps, should (perhaps) be identified in this fashion:
- Capt R.H. Kaufman (01 Dec 43 - 11 Dec 43)
- Capt R.H. Kaufman (24 Dec 43 - 08 Jul 44)
And the Lozinski Crew should (perhaps) look like this:
- 2nd Lt S.J. Lozinski (24 Feb 44 - 14 Jun 1944)
- Capt S.J. Lozinski (25 Oct 44 - 16 Feb 1945)
This, however, begs some questions from me. Did the crews managed by a given officer overlap? Following the examples above, did each crew exist similar to this illustration?
Capt. Alfred Beta:
< -- -Crew A -- ->< -- -Crew B -- ->< -- -Crew C -- ->< -- -Crew D -- ->
Or was there overlap and reuse of crews and, therefore, it looked something like this:
< -- -- -- -- -- -Crew A -- -- -- -- -- ->< -- -- -- -- Crew B -- -- -- -- >
< -- -Crew C -- -> < -- -Crew C -- -> < -- -Crew D -- ->
In the second example, Crew C flies twice, is used while both A and B exist and overlaps with both A and B.
So, which scenario better represents how crews saw service? Did a given airman only manage one crew at a time? Or were some crews reused over and over again at different points on the time-line?
The answers to these questions will best tell me how to manage crew information, so please feel free to speak your thoughts. 🙂
Ed.
|
rick_kaufman
10/3/2010 9:56:32 PM | Ten unique crews per pilot may not be adequate.
Don Byers supplied me with listings of the crew members who flew with my father, less March 1944. I counted 14 unique combinations of personnel for my Dad's crews, and one month of his service were not accounted for.
I don't know for sure if Dad is an outlier, but he would definitely be a case that shows that more than 10 crews are possible...
Rick Kaufman
|
EDanaII
10/4/2010 6:48:25 PM | Hmmm... OK, even if your dad is the exception, I'm assuming we want to track that information. This means I need to change how the DB identifies a crew. And, once that is changed, it will impact the rest of the site.
I can manage this somewhat by creating a secondary means (secondary key) of identifying a crew and leave the primary means (primary key) in place. Creating the secondary key while leaving the primary key will keep me from breaking the site. I can then phase the primary key out and, once gone, make the secondary key primary.
This still means some rework of the site, but this will make it more manageable.
So, do you know if any of those crews your father flew with overlapped or were reused? Did your father fly with one crew, then another and then fly with the first crew again? This knowledge probably isn't necessary for me to complete the task, but I think it's good to know and could affect how I approach things.
Ed.
|
donaldbyers
10/5/2010 11:23:39 AM | I would think that both examples are possible! Take for another example my uncle he only flwe one missions with M.M. Cain on 24 Aug 1944. He should only show up for that mission and not be,as listed, part of the original crew but still makes sence to appear on the mission of that day. Other than trying to break the DB we should look at how are we going to display the data for daily missions.
Don
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|
win-win
10/5/2010 1:18:29 PM | Hi Ed (and Rick & Don):
'Not sure if this is pertinent, but ...while you're at it... I thought of three kinds of 'other' Crew info that would be great to be able to include, but may or may not be within our DB's 'real world' ability (or records to confirm). These examples relate to my Uncle's 613Sq 'replacement' Crew:
- - On their 1st Mission in August, 1944, my Uncle's 1st.Lt. Nelson Crew's CP, Lt. Nevois, flew with another Crew, and that Crew's CP flew with Nelson (so both planes' drivers had 1-experienced and 1-newbie). I don't know if that was typical for replacement Crews.
- - I know Nelson Crewmen S/Sgt. Dorris RAD; and S/Sgt. Majeski BTG, flew an additional mission with other Crews. I think 'fill-ins' like this were probably common (but don't have other Crews' info to confirm either way).
- - Now that the 401st was flying with 9-men on Missions (from mid-1944+/- on), the 10-man Lt. Nelson Crew had the two Waist Gunners 'trade-off' - flying on alternate Mission days. So the WGs, to keep-up their Mission count (with the rest of the Crew), flew with other Crews (when 'not their turn' flying with Nelson). I think that was common, but I don't have specific info for other crews).
It was from the Mission Loading List originals at the NARA that I found this info, demonstrating the value in supporting Don's '401st Mission Reports' upgrade' Project.
Thanks for your working so hard to make the DB work better, and i hope the DB framework can support these kinds of records,
Win
|
EDanaII
10/5/2010 9:27:21 PM | Excellent, Win, thank you. That actually answers my question regarding crew reuse. It means that different crews were likely reused and that including dates as part of the name is probably not useful. I'll continue to stick with the method Larry originally established by simply inserting a number in the crew's name.
@ Don
That would simply be reflected in the DB as another crew for the pilot in question. As to displaying data... one thing at a time, my friend. 🙂 First I gotta figure out what may or may not break, then, when all's cool, I can figure out how to present the data.
Ed.
|
donaldbyers
10/8/2010 2:49:51 PM | I would look at this as Mission->Pilot or the other way around Pilot->Mission. However, the Crew Data Page is flawed and the reason I was thinking of Data display. I know I seem to always go to the end point but it's how I think. If the number of times the pilot can be associated is currently 10 then why is there a limit in the first place which is hard coded?
Don
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|
EDanaII
10/9/2010 3:39:27 PM | Actually, I am thinking of this as Mission->Pilot. More correctly, if you look at the chart I provided above and follow the lines, you get Crews_Members -> Crews -> Crews_Missions -> Missions. Since a Crew Member can be a pilot and the end of that path is Mission, simplifying you get: Missions -> Pilots. 🙂
Regarding your other question, when Larry created the database, he included a "smart key" as part of the Crew table. A smart key is a value in a database that stores more information than one would normally expect. For example, the Crew Members table has a field in it called "MemberCrewGrade." This single column has a single value: the highest grade that person was elevated to at the end of their service. It is not a "smart key." The smart key that Larry created has three pieces of information in it: the Crew Type, the Member ID of the Crewman in charge of that crew and a number identifying a crew that person managed. It is formatted like this: 12271. The single digit on the left is the crew type. The single digit on the right is a number that represents the crew that was being managed. And the middle number, 227, is the Member ID of the person managing that crew. (In this case, Rick's father.) These positions are fixed, therefore, the right position that identifies crews is limited to the values 0 - 9 and this limits us to only ten possible crews per person.
As I mentioned above, I can fix it by simply treating the smart key as a surrogate key. (A surrogate key is simply an arbitrary identifier that has no meaning beyond identifying the record in question.) I can then add two new columns to the table that represent the Crew Type and Crew Number and create a secondary key -- which behaves exactly like a surrogate/primary key -- that will play the same role as the smart key. With the Crew Number freed from that fixed position on the right of the smart key, we will no longer be limited to 10 crews per pilot.
Probably more answer than you expected, but... I felt the need to say it. 😉
Ed.
|
donaldbyers
10/9/2010 4:46:11 PM | Actually that was great the way you explaned it and was easy to understand why Thanks for that. I had never heard of the smartkey but have the Primary Key when I was working with MSAccess.
Don
Sgt. Donald C. Byers, 613th Bomb Squadron, Togglier, 42-97344 Carrie B II, KIA 08/24/1944.
|