CODEWITHSUNDEEP
database-designrelational-database
machinery
machinery

Reputation: 6290

Database scheme for storing sensor measurements

I want to read different bio-sensor as well as tablet-sensor data and store them in a SQL database. The bio-sensor data are e.g. the skin conductance, the heart rate, the heart rate variability and so on. These are just numbers (with an unit). Regarding the tablet-sensor data I have touch input, acceleration, front cam, stylus input and so on. Basically these are also just numbers except for stylus and front cam.

Regarding the SQL database scheme I thought of creating a table with the columns timestamp, user ID, tablet ID, sensor ID, unit and value. The key would be (timestamp, user ID, tablet ID, sensor ID) because there can be measurements of different sensors at the same time. I'm recording all sensor values with the tablet (also the bio-sensor data), so there is always a tablet ID. That also means that the database is hold on the tablet. I'm using Android.

I don't know if these makes sense. Perhaps it would be good to have a separate table for every sensor. How would you make such a scheme or is there another proposal for a good scheme?

Second, how should I store stylus input and front cam (video) recordings? I thought of storing the video recording not in the database but external and then somehow linking from the database to this external file (e.g. through the file name).

There will be a lot of data to store. I will collect sensor data over 60 minutes and there are several values to write every second (e.g. for the bio-sensor data every 1/10 second there is a new value). So it could be a problem that the database gets too big or is too slow. By the way, I only want to write to the database. Reading the database I will do in a second (offline) step.

Upvotes: 1

Views: 2430

Answers (2)

Jay
Jay

Reputation: 27492

Some thoughts:

I'd assume that the data for each sensor always uses the same unit. That is, if sensor #7 gives a value in ohms today, it's not going to give a value in millimeters tomorrow. More plausibly, I suppose there could be some setting to let you switch between inches and centimeters, etc. But if the unit is always the same, then don't store the unit in the same table with the values. Have a separate "sensor" table that gives the unit for that sensor, probably other information about the sensor, like a name.

Whether to have one table per sensor or a separate table for each sensor: Big advantage of one table is that if you add a new sensor, you don't have to change the schema, it's just data: Add a record to the sensor table and you're done. If there's lots of data this table could get big. But each record is pretty small: I presume sensor ID and tablet ID can be int's. User ID might be an int or a guid.

I've never tried to store a video in a database. I've stored images and those are huge compared to other data. A video is normally at least megabytes. My gut feel would be to store them as individual files and just store a filename. I'll happily yield to anyone who's actually tried putting videos in a DB and can say how it turned out.

Upvotes: 1

user1919238
user1919238

Reputation:

I would lean toward a separate table for each sensor. The reasons:

  • It doesn't seem like you would typically want to analyse data for different sensors together, since they represent different things.
  • You wouldn't have to store the unit or sensor ID. The storage savings may be significant, if you are dealing with a large volume of sensor readings--especially on a tablet environment with limited storage.
  • A shared table may have locking problems if you are trying to insert records from multiple different sensors at a high rate.

I would also create a single table to capture one period of interaction with the device. Let's call it "session".

This would provide a key for the other tables, and also allow you to define what the video and stylus data files are associated with that session.

So you would have something like this:

session (
    session_id,
    user_id,
    tablet_id,
    video_file,
    stylus_data_file,
    session_start_time,
    session_end_time,
    ... any other session-level data ...
)

And then for each sensor have a table like:

heart_rate_readings (
    session_id,
    timestamp,
    value
)

Note: I don't know enough about the stylus data to give an informed opinion. The above assumes it will be stored in a separate file. But if it is just a stream of coordinates you are receiving, you might consider also storing it in a separate table in the database.

Upvotes: 0

Related Questions