Migrating/Updating my TinyDB MAC API Introduction – Part 1 – Database Setup

On By dustin

As mentioned in my previous post, TinyDB eventually presented the same problem that hosting a CSV file did for my project: it became large and unwieldy. TinyDB works by creating a json file that I could then query by searching for the most recent datetime or specific datetime to get a list of mac addresses at a specific time.

When looking to see how I might store mac addresses in MySQL, I initially thought I would follow a similar technique as TinyDB: a simple two column database that would have a datetime column and a macaddress column. My thought was that I could have a select statement that would find a specific time for me and then provide a list of mac addresses online at that time.

I was worried about what data types would be best in my situation and started to research “how to store a list or dictionary in a MySQL table.” Several old stack overflow articles broke down why this was not ideal and recommended the use of a junction table for situations like this. I worked through some of the examples they gave but had trouble grasping the concept until one of the articles recommended reviewing the Satisfying the 1NF Database Normalization page on Wikipedia.

The article started with an “unideal” table:

and then gave instructions on how to move towards the more ideal use of a junction table referencing two individual tables:

The example instantly clicked and gave me some better ideas on how to structure my data.

Ultimately I decided that I would need three tables:

  1. A simple datetime table: Stores dates in several different formats (datetime, year, etc).
  2. A mac address table: Stores previously seen mac addresses, descriptions of those macs, etc.
  3. A junction table that references both of the above: stores individual datetime/mac address information.

With the creation of these tables and ideas I was able to formulate some of the next requirements for my project like:

  • How do I handle unknown/ new mac addresses?
  • How can I search for the time a mac address was last seen?
  • How can I search for unique mac address across a certain time period?

With my table setup, and some example data punched in, I was ready to move onto creating the SQLAlchemy structure to begin the simple queries I’d need for my project…more on that next week.