Large database design

database-design

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.

Best Answer

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 : ]

Related Solutions

Mysql – Large scale, hard to model design advice needed

Store the data in flat text files which are uniquely named based on each system, not in a database.

Databases (particularly relational ones) excel at storing data in tables with a consistant schema, relating those tables to one another and querying them in arbitrary ways. What you are describing is (based on my reading) basically a web page to display text files. There is only one way to access the data (via what is effectively a unique key, the system name), there don't appear to be any relationships, and the data will vary considerably from system to system (hence your issue with lots of columns).

So I don't see much benefit in using a relational database (a document database such as RavenDB, CouchDB or MongoDB may be a better fit, but even they are likely to be overkill for what you describe).

If you're really keen on using MySQL, just store the contends of the file in a table you can lookup via a name:

system_name varchar(255) NOT NULL Primary Key
file_type char(2) NOT NULL = 'F' or 'IF' (for frequent or infrequent)
data Longtext NULL

And parse your textual data in PHP (or simply display it).

You could store your data exactly as you receive it, or process it into XML, JSON or YAML to standardise it.

I just noticed the show common properties requirement.

Perhaps you could have columns in your table for those common properties, but not all the properties. You can quickly query the common properties while still being able to view the full data. (I don't have direct experience with any of the above document databases, but I believe all of them will let you index arbitrary fields in your documents, which will be much more flexible than a fixed number of columns).

If you really want to stay with MySQL, you may want to look into the Entity-Attribute-Value pattern. (IMO, EAV is an anti-pattern, but it should be OK just for the common properties).

Of course, if display time isn't really a big worry, just opening and parsing 1000 text files isn't going to take too long (I'd expect ~10 seconds).

Sql-server – Database design for Magazine website

I am finding it hard to design a database that can help me fetch menus from different table for example as show in image below

To me this sounds like you are trying to make your database design match the menus in the UI layout. This might work until the UI changes. Then will you change the database structure?

It might be better off to create proper tables based on the data itself, and then design some views that are more accessible by the UI.

Best Answer

Related Solutions

Mysql – Large scale, hard to model design advice needed

Sql-server – Database design for Magazine website

Related Question