Relational Database Design Question

Raskolnikov

Raskolnikov

Well-Known Member
Okay, I know most of you won’t know what I’m talking about. But there’s always a chance that a few of you will be able to help me. I haven’t been able to find the answer online so far.

I’m trying to build a relational database with MS Access for my logbook. Now, I know there are other ways to log your time digitally: www.logbookpro.com, Excel, etc. I want to do this in Access so I can get some more experience with databases. I figure making a logbook would be a good break from the database I’m trying to wrap my head around at work.

Specifically, I’m not sure if my table relationships are all normalized or if the relationships are in order.

I've attached a screenshot of my tables.

I'm not sure if it's a good idea to have my tblFBOs link to two other tables with two other primary keys. Also, I'm having second thought about my tbInstructors. Maybe I should have a tblPilots and then have a yes/no field for instructor. Suggestions?

So, anyone here use Access or another db program for your logbook???

A screenshot of your table relationships would be helpful.

Thanks in advance. And my apologies to those who have know idea what I’m talking about.
Ooo, sorry 'bout the poor readability of that screenshot. I’ll see if I can get a better one.
 
If you have logbook pro or forpilots.com you can get a crappy PDA (I used my cell phone) and plug it in to your computer and it will upload the info.

You can separate time by type, night, IFR, VFR, solo, dual received, dual given, etc... you get the point. I keep a printed copy in my car and at work as well as the hard copy on my desktop. I didn't start using it until about the 1200 hour mark and it took me a looonnng time to enter the info. Now it's a piece of cake. Not sure that's what you're looking for, but going digital is sure easier. I stil log it in my Jepp book as well just in case.
 
Thanks for the tip. I will give logbookpro a try and see if I can see how they setup their tables.

The whole point of this actually was to try to figure out where my mistakes were in my design. I know this isn't a db forum, but you never know...

Again, thanks!
 
It's been a long time since I messed with Access, but intuitively I don't think you need to have 2 separate airport data tables - I think that's partially defeating the purpose of having a relational database.

In other words you link both "takeoff airport ID" and "landing airport ID" to the "airport" table through the "ID" field. Same would be true for the single "airport" table - it would have an "FBO ID" field linked to the "FBO" table. You'd need to design the "airport" table with fields for FBO1, FBO2, etc. to allow for those relatively less frequent cases where there is more than one FBO on the field.

I agree with your "instructor" notion - could just have a "pilots" table and then have one of the fields identify the ratings or yes/no for instructor.
 
JHines said:
I don't think you need to have 2 separate airport data tables
Click to expand...

Agreed.

From my limited experience, it looks like you've got a pretty good start. A suggestion:
Instead of using an "aircraft category" table, why don't you make the category an attribute of each aircraft type. I think you could make items such as "high performance", "turbine", "complex" etc attributes of each aircraft type (by using a yes/no function). This would make your reporting more accurate, since it would reduce the possibility of data entry errors.

In addition, it might be easier to create an "aircraft type" table, rather than N-number table. (Unless you wish to track the specific attributes of each aircraft for some reason - such as tracking your experience with various avionics or year of aircraft) You'd still be able to get reports by N-number if you wanted them, and I think again you'd save some data entry headaches.

Just some ideas, but only you know what you want/need.
 
You're off to a good start but quite honestly, a logbook doesn't make a terribly great database project due to its lack of many-to-many relationships, which is what databases excel at. You've got plenty of one-to-many relationships, but no many-to-manys. The fact that a logbook can be kept efficiently in a single spreadsheet shows that it's data is already normalized; for example, you're not using multiple rows to capture a single flight.

If you look at an existing database-driven log application like Logshare.com, the only reason it requires a database is because it tracks not just one pilot, but hundreds. Some of whom might fly the same airplanes. But I'll bet if you asked Logshare creator Dan Checkoway, his database isn't probably more than a half-dozen tables, if that.
 
Matt I may have to retain you for an upcoming project. I'm not sure when but you da man when it comes to relational databases.
 
I am retainable...and retrainable! And I speak SQL!

Hairiest database design I've done so far:
 
Hey thanks for all the replies everyone! I just started working with databases a few months ago. The more I lean about them, the more I realize how much I don't know what the heck I'm doing.

Yeah, it certainly makes sense to only have one airports table.

I do have one many to many relationship: the tblPassengers and tblFlights tables are linked by the tblFlightDetails table. Many pax on a flight, and a pax can take many flights…

What about the idea of having a field link to two separate tables, like I have coming from my AirportCode field on my FBOs table. I know my two airports tables are a bad idea, but there’s an example at work I’m thinking about. We have a tblStates table that links the StatesID filed to several other tables. I work for an insurance co and it’s important for us to know what state the policy is sitused, and the state where the claimant lives. I haven’t been able to find anything specific or a rule against it. Is it generally not done? Why?


Wow, I really got some great responses from all of you. My wife said it was a bit foolish posting a DB question on an aviation site. What the heck does she know?

JC rocks again! Thanks for all the great tips!
 
You must log in or register to reply here.
Back
Top