3

The end goal is to have a variety of data sets that can all be graphically plotted against eachother.

All data should correspond to a date, so that when plotted against each other, they show the correct relationship through time.

The key feature though, is to be able to add a new parameter to log at any time which will then be logged simultaneously and will then be able to be plotted against other data sets (from the time the later began onwards).

As a sub point, it also needs to be able to deal with sparse information which is sometimes available and sometimes not.

An example of the data can be seen below. C is sparse data and D is a data field added half way down the table.

Example of data

I have considered SQLite but the thought of all the column changes due to columns added by myself and users didn't appeal.

Currently, I am thinking of creating an object which keeps track of time and contains a data field list of different data lists such as A, B, C and D as in the image. The thinking being that to add a new data field, I could just add a new parameter (e.g D) list to the data field list which already contains A, B, C.

Then I could just serialize this into JSON to keep it persistent and when I want to graph anything with sparse data, I could interpolate it.

That is my current solution but I would very much appreciate any opinions on better practices or flaws in my current solution.

1 Answer 1

1

That's how I would approach this.

Two tables in the database.

1. log

[id] [timestamp] [dataset_id] [value]

  • [id] - auto-increment
  • [timestamp] - obvious
  • [dataset_id] - numeric ID of the dataset (related to datasets.id)
  • [value] - the numeric value for your graph (or all these 9, 7, 8 from your drawing)

2. datasets

[id] [name]

  • [id] - that's dataset_id from table log. Just an int.

  • [name] - that's the name of the dataset: "A", "B", "C" etc.

Now when D needs to be added, you don't add a datacolumn anywhere - just another row to table datasets.

I don't know the details (such as the expected size of your data), but I don't like the thought of using JSON. It doesn't feel like the right tool for the job.

3
  • Hmm, interesting. One of the considerations as well is that I will likely be wanting to view, edit and create these logs online. That was a factor in the JSON as well as familiarity. Amount of data would be able around 500 logs. Will SQLite be able to handle the occasional null values?
    – mgibson
    Oct 15, 2013 at 18:23
  • Of course SQLite can handle nulls, although within the requirements you've given, I see no need for nulls (if there is no data for a given date, such as no D for dates from 1 to 3 on your drawing, you simply don't put any corresponding records in the table). Keeping the data in SQLite doesn't rule out serializing it to JSON when there is a need. But admittedly ~500 records is a little, so storage format is not an issue. Learning more about your considerations I'm getting an impression that you're giving too little context... Oct 15, 2013 at 20:25
  • Cheers Konrad, it sounds a more sensible approach! I'll give it a go.
    – mgibson
    Oct 16, 2013 at 9:27

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.