1

I am new to database design and despite my research I can really only find basic examples on design which does not help for my situation.

Currently using MySQL

The problem I have is as follows:

I am building a site to manage aircraft information, then display that information and how it changes in the form of graphs (things like air speed, plane speed, wing tilt, ect ect).

At the moment I get all this data in CSV form, but different planes give different data (for example one plane might not have wing tilt).

At the moment all I have set up is a table with userID, username, and password.

The concept I do not understand is how I can store something like planeSpeed, especially since it would be a bunch of numbers because it is over the course of the flight (it gets a value every minute).

Also some users would have multiple planes.

So I am stuck visualizing this as a column would be airspeed and then have a ton of numbers in it?

Or would I somehow build a separate table for each plane and instead of userID I would have time?

Very unsure how to design this, any help or tips would be a huge help, sorry for sounding so inexperienced have never built more than a simple login before.

Improve this question
1
  • As a simple example lets take "airspeed" table - it will have columns (aircraft_id, time, airspeed) - aircraft_id is number referencing row in table of planes (there is "static" info about the aircraft - type, age, user...), time is the time of airspeed measurement and airspeed is the value - for each plane you have lot of rows, one for each time, storing one value. If you get multiple data each minute, you can extend that table with another columns (height, latitude, longitude...), thats ok, but each row contains only data of one specific time for one given plane. You will have many rows :)
    – jkavalik
    Jun 8, 2015 at 19:15

2 Answers 2

Reset to default
2

As user1786423 said in your question's comment, you will probably have a User table, an Aircraft table and a table for each type of measurement.

As a minimum, User would have an id, say idUser. I'd imagine there would be more static data about a user in there as well.

As a minimum, Aircraft would have an id, say idAircraft, and idUser. idUser would reference User.idUser. I'd imagine there would be more static data about an aircraft in there as well.

The AirspeedMeasurement table would have, based on your descriptions, columns like idAirspeedMeasurement, idAircraft, time, airSpeed. idAircraft would reference Aircraft.idAircraft.

You may want to have a parent table for all of the measurements. For example, an AircraftMeasurement table would have the columns idAircraftMeasurement, idAircraft, time. Then, the child tables would have a primary key that references idAircraftMeasurement, and wouldn't have to each store idAircraft and time.

An ER diagram would look something like this: enter image description here

Some sample data:

In this example Aircraft 1 records PlaneSpeedMeasurements and WingTiltMeasurements, whilst Aircraft 2 only records PlaneSpeedMeasurements. enter image description here

Let us know if something is unclear : ]

Improve this answer
7
  • I would use separate tables for each measurement only in case you can get different set of measurements at different times. But I would personally prefer one table with nullable columns unless there were some reasons against it.
    – jkavalik
    Jun 9, 2015 at 6:08
  • Does the layout let the user search by time? I understand everything except the way AircraftMeasurment is set up, don't quite understand the point of that table. Also just to confirm these are child tables right? I would then JOIN them later in query.
    – Lain
    Jun 9, 2015 at 13:33
  • Yes, you can search by time, for example: SELECT * FROM AircraftMeasurement WHERE timeOfMeasurement = [some time] Yes, AirSpeedMeasurement, WingTiltMeasurement, and PlaneSpeedMeasurement are child tables of AircraftMeasurement. There are a couple of reasons you would have the AircraftMeasurement parent table - 1) it abstracts out the common fields (idAircraft, timeofMeasurement) 2) it would be possible to make a foreign key to any AircraftMeasurement, if that was ever needed. Yes, you would need to join the parent with the child tables to get all the data.
    – Joe Borysko
    Jun 9, 2015 at 23:05
  • You can of course not do the parent-child table approach if you want. Just remove the AircraftMeasurement table and place the idAircraft and timeOfMeasurement columns in each of the child tables.
    – Joe Borysko
    Jun 9, 2015 at 23:11
  • @user1786423 I see that as being viable too.
    – Joe Borysko
    Jun 9, 2015 at 23:14
0

As user1796423 suggested, you can combine the different aircraft measurement tables into one table. This would make most sense if the different measurements were all coming in at the same time. The columns representing measurement values would have to be nullable if they are given by some aircraft and not others.

Here's what the diagram would look like:

enter image description here

Here's some sample data:

Aircraft 1 gives wingTilt, planeSpeed, and airSpeed whilst Aircraft 2 only gives planeSpeed and airSpeed. enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.