Hierarchal data and sqlite

I’m in the design phase of a program to replace a task currently being managed over multiple spreadsheets. I am pretty much a beginner, although I’ve written a few scrapers, data parsers and other bits.

The program will take data from a csv, clean it up and sort it into a 4-level parent child hierarchy. I’m planning to store all of this in a sqlite database as I’ll be wanting to use the data again between sessions. Once the data is loaded, the program will need to be able to make quick, complex search queries/filtering, as well as crunch numbers (for example, comparing a new number with the previous value in the same category

I think I can accomplish this all with sqlite3, pandas, numpy, a gui framework I still need to choose and some custom functions. My question is around how I’m using sqlite as it doesn’t seem like a tailored fit.

Here’s a mock dataset:

Food > Cheese > Cheddar >  {20/09/2019:£5.50,19/09/2019:£5.25}
Food > Cheese > Brie > {20.09/2019:£6.20, etc..}

So I’m thinking about just updating the 4th column with the new values each time there’s a new csv with updated data, to avoid an excessively bulky database file but it doesn’t feel like sqlite is designed to be used this way. Dictionaries are not recognised as a sqlite datatype so I was thinking I could write a function to unpack that column.

First question: Is this okay, or is there another way I should look at accomplishing this? Second question: Assuming this is possible, it seems like it would really put a spanner in making the kind of quick database selections I’d like to make. I don’t see a simple way to say select all cheddar prices from before 19/09/2019, for example.

The more I think about it the less practical this seems. What’s the alternative, a big text file full of JSON? Using sqlite and leaving tonnes of duplicate data in there?

You could look into MongoDB or CouchDB if you want to store a bunch of data in a single row.

SQL is generally best at storing structured data with lots of rows for quick lookup.

An sqlite database might look something like this:

Table: products

id (sqlite has auto-incrementing counters)	name (varchar)
1	Food > Cheese > Cheddar
2	Food > Cheese > Brie

You might not even need the id field if names are guaranteed unique. But it is good to reduce data duplication. For example, if you recategorize it later under Food > Dairy > Cheddar, or change the name in some other way, having the id field means it's only updated in one place.

Table: prices

id	product_id	date	price
1	1 (=cheddar)	20/09/2019	5.50
2	1	19/09/2019	5.25
3	2 (=brie)	20/09/2019	6.20

This allows you to do things like select * from prices where product_id = 1 to find all prices of cheddar. Add order by date desc and limit 1 to get the most recent, etc.

That's generally how SQL is used.

You could just store a big JSON blob in a field to collect the dates/values. It might work fine for small datasets, but could get ugly quickly if you're updating daily.

Hierarchal data and sqlite

comments