On the right track?

[hthg21]

I am in the process of creating a database and I know that the most important part is getting the table structure setup correctly. If anyone has any suggestions for me I would very much appreciate them. In a nutshell here is what I'm doing this for:

We are an Air Support Unit that conducts Law Enforcement & Search and Rescue missions. I want to track each flight, the type of flight, the flight time, the crew of the flight, each incident that occurred during the flight.

I also want to keep track of the crews currency for several different functions like Night Flights, Operating the Hoist, and training that they complete.

I also want to keep track of Squawks (things broken on the aircraft) for each aircraft, when they occurred, when they were fixed and by whom.

Final thing is keeping an Inventory of a bunch of different items, some that have expiration dates.

I have attached a snapshot of what I have so far.

Thanks in advance for your assistance!

 

[MarkK]

I'd expect a squawk to be related to the aircraft. Maybe it is also linked to contacts ReportedBy and ClearedBy, but a squawk belongs to an aircraft, no?

In tblFlight you have a field pilot. How do you intend to handle other crew? Might better be handled how you are handling passengers, many-to-many with links to the Person/Contact record.

I'd rename Flights_Passenger to just Passenger, and call the primary key PassengerID. Foreign keys can be FlightID and ContactID, and that's how I'd handle crew too, including pilot.

Name all your tables in the singular. Incident. Flight. Contact. When you mix and match like that you'll always get it wrong half the time. If they're always singular, you always get it right.

Sometime you don't need to link to a table with just one field. Aircraft makes sense for a table, since you have other data you haven't added yet, like model and tail number, but Location and Role? Maybe just put a combo on those fields and set LimitToList = True, you know, it's one field. It's a bit of overhead to have a whole table for that, but ...

Anyway, there are a few quick thoughts

 

[hthg21]

Thanks for the suggestions lagbolt.

Squawks don't always belong to an aircraft, every once and a while it is just a piece of equipment.

I was going to handle crew just like any other passenger, except there is a Yes/No for being a hoist operator or pilot.

Since this won't be a huge database I wasn't concerned about the overhead, but good call.

Thanks again!