6

I am struggling to design the correct database / structure for what I'm trying to do. Let me try and explain the situation.

Background

We are a group of enthusiasts who monitor air traffic via ADS-B reception. If you have seen www.fr24.com then you have a pretty good idea about it all. All planes broadcast a signal containing the unique HEXCODE identifier, together with speed, position, etc.

We who receive the signals store this information in a database of sorts, all based on the HEXCODE that was transmitted by the big bird in the sky. Depending on what hardware and software you use, that determines the db that the software creates.

But many times you see a "new" plane and the db has no reference (prior data) for it, say what model or airline it is, so it ends up as an "unknown". Each one tries to update the unknowns from a variety of different sources on the internet, so when next you see it, you have a name and model and airline etc. attached to it. This is just for own viewing on local computer.

That is the background of the project that I'm busy with.

Data

We are now trying to consolidate all the data each user has gathered into one database, update what is missing, help each other with missing information and try to go forward with an up-to-date db. This is where it becomes difficult now.

I have managed to get 6 different datasets (lets not call them databases), each with its own data in many different fields. I have stripped down the CSV/xls files to contain the important fields: (MODATE is the last seen/modified date)

+---------+---------+-------+-------+------+-------+--------+----------+---------+
| HEXCODE | LASTHEX | REGNO | MODEL | TYPE | MODEL | SERIAL | OPERATOR | MODDATE |
+---------+---------+-------+-------+------+-------+--------+----------+---------+

Datasets Bravo, Delta, Hotel, Sierra (let's name them) contain:

Bravo:

+---------+-------+-------+------+-------+--------+----------+---------+
| LASTHEX | REGNO | MODEL | TYPE | MODEL | SERIAL | OPERATOR | MODDATE |
+---------+-------+-------+------+-------+--------+----------+---------+

Charlie (the most correct source in terms of what it contains):

+-------+-------+------+--------+----------+
| REGNO | MODEL | TYPE | SERIAL | OPERATOR |
+-------+-------+------+--------+----------+

Foxtrot (the most accurate mapping of HEXCODE to REGNO available):

+---------+---------+-------+-------+------+-------+--------+----------+
| HEXCODE | LASTHEX | REGNO | MODEL | TYPE | MODEL | SERIAL | OPERATOR |
+---------+---------+-------+-------+------+-------+--------+----------+

So I created a DB, called it "aircraft1" with tables named:

  • Bravo
  • Charlie
  • Delta
  • Foxtrot
  • Hotel
  • Sierra

and each of those tables contain the same fields:

+---------+---------+-------+-------+------+--------+----------+------------+
| hexcode | hexlast | regno | model | type | serial | operator | moddate    |
+---------+---------+-------+-------+------+--------+----------+------------+

... though some of the fields are NULL (based on the source where it came from, and if that source has that data. I imported the relevant datasets into the relevant tables.

Queries

Now at this point I can ask the DB anything with my limited SQL-speak:

select * from {table} where {regno/hex} = {whatever}

...and it gives accurate information (compared with the different data sets).

The ultimate purpose of all of this, is to combine all the data and create a current and complete database/dataset of everything, where the selection would be something like:

  1. Select a HEXLAST from BRAVO/DELTA/HOTEL/SIERRA
    check in FOXTROT if it's a HEXLAST or HEXCODE to find the REGNO
    then goto CHARLIE and pull the other fields in.
  2. If unable to find the HEXLAST in FOXTROT, check in others (datasets B/D/H/S) if there is a REGNO, if found, goto CHARLIE and pull the other fields in.
  3. If not found in CHARLIE, check others (datasets B/D/H/S) for the most current MODDATE and select other fields from that dataset.
  4. If no match found at all, list it as UNKNOWN with the MODDATE (last seen)
  5. Create a new table with the updated information.

Questions

Speaking to peers, they advised me I'm silly to have created tables based on the data sources, I should rather have created tables named: hexcode, hexlast, regno, etc. I should also have created a Primary Key that is a number unique to every record, rather than a Primary key within the table itself (where depending on the data in the table, some PK's are the REGNO, others the HEXLAST, etc).

Basically doubting what I have done thus far, asn wondering if ever I would be able to get the desired output from all of this.

How would YOU (with your experience and expertise) would have gone about this?

What I have created thus far, I am more than willing to share with anyone willing to help with this project. The information in there is basically in the public domain, so its nothing uber secret/confidential at all).

My strength lies in PC's, networking and wireless networks, not in databases. Google is helpful to a great extent, but when it comes to custom/specific stuff like this, finding the right answer/solution/method ... thats not really out there.

Any help and advice, pointers and slaps in the right direction, will be appreciated.

1 Answer 1

1

It is 'rarely' good form to have two tables with the same set of columns. It is 'usually' better to have one table with an extra column that specifies 'dataset' the row belongs to (Bravo/Delta/...).

Often it is helpful to think of the schema (table definitions, etc) from an "Entity/Relation" point of view. Start with the Entities such as (I guess) Signals, Aircraft, etc. Where necessary, use "many-to-many" relation tables. Think of students:classes. Entities are students, classes, teachers, etc. The Students_Classes relation table says which class(es) each student is in and which student(s) are in each class.

3
  • Paul White : Thanks for the edit and beautifying of the post. Rick: Thanks for your reply and pointers. So I shoud redesign the lot, create tables as you suggested (based on the data I have: hexcode, hexlast, regno, etc) ..... in my raw data (coming from XLS) create a column in there depicting the source of it... and import it as such into the new schema. So have multiple colums thats populated by thousands of rows from all the different sources. Thus, avoiding the need to run queries accross tables, speeding things up and keeping it nice and clean in there.
    – Herman
    Aug 30, 2015 at 9:30
  • 1
    Yes, thanks to Paul, I have a clearer picture. 9-10 columns, some of the NULL due to missing info -- that sounds reasonable. The extra cols are source (bravo, ...) and, if necessary, an auto_increment id. Plan on "cleaning" up the data as you load it.
    – Rick James
    Aug 30, 2015 at 18:15
  • To piggyback on this, a very good general idea to keep in mind when designing anything is the Single Responsibility Principle. In the data world you can translate it to: "A place for every thing and everything in its place". If I have a favorite coffee mug that I'm looking for in the real world, I have to look in every room for it. In the digital world, I can just look it up in "mugs" and see that it happens to be in my office.
    – Greg
    Sep 18, 2015 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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