Best way to store this data

PHP
1

Hi

Just wondering what would be the best way to store this data in a database.

Basically i need to store many different sets of data, with each set consisting of different readings taken at certain time intervals, if that makes sense.

ie. a set of data might have this form:

00.00 reading1: 45, reading2: 27, reading3: 34
00.05 reading1: 46, reading2: 29, reading3: 30
00.10 reading1: 45, reading2: 17, reading3: 24

i dont need to store reading titles (“reading1”).
Also there could be hundreds of intervals, the no. of time intervals may vary, and need to store additional info for each set of data.

Was thinking of storing the readings in a kind of csv format but not sure if that is best.

Any help is much appreciated!

2

Does the “additional info” pertain to each reading number, or to a particular time and set of readings?

Can you do a summary for a particular control time

00.05 1: 46, 2: 29, 3: 30 “reading 2 was below normal because someone switched the heating off”

OR

Must you allow different comments on each Reading/Timeframe ?

00.05 46 “no comment”
00.05 29 “heating was off”
00.05 30 “someone added arsenic”

Database is the way to go if you want to slice and dice the data, frequently create reports etc

3

Sorry didnt make it that clear.

Say there is a “trip”, and there will be many of these trips.
For each trip there will be various readings taken at a fixed time interval. The number of readings is constant but the number of time intervals may vary, and there will be lots of them. I don’t need to have a reading description as i know what each reading is.

For example: (readings are separated by commas)
trip 243:
00.00 34,65,34,26
00.05 25,76,56,34
00.10 34,25,46,53
00.15 27,54,87,24
… (possibly hundreds of sets of readings)

Each trip needs additional information along with it. The readings will not need to be edited at all.

I definitely want to store it in a database, but im justing wondering how best to store it in the database.
For example, one option is to have 2 tables:
table1: trips
tripid, name, otherinfo, time_interval
5, Trip name, something else, 00.05

table2: tripdata
id, tripid, reading1, reading2, reading3
1 , 5 , 34 , 54 , 65
2 , 5 , 65 , 24 , 35
3 , 5 , 26 , 54 , 65

but i think there may be a better way since these readings are fixed and dont need to be individually edited etc…

4

a trip is unique and each trip could have many readings.

so I would have a 1 to many relationship between a trip and readings table.

tblTrip

fldTripId PK
fldDate
fldName
fldDestination
…

tblReadings

fldReadingId PK
fldTripId FK
fldRead1
fldRead2
…

5

Those look like sensor readings to me, is that right? If so, I imagine that each ‘trip’, could potentially include readings from many sensors?

I’m rubbish at this type of stuff, but what about…


trip.id
trip.date

reading.value
reading.trip_id
reading.sensor_id

sensor.id
sensor.name
6

time_interval

Can you explain what that is?

7

Common data that will ALWAYS exist and that you might want to search on should each have their own field in the database. Uncommon or optional data is up to you what you do with it, but lately what I do is create a ‘text’ field and then use the PHP serialize() function on an array that contains the remaining data. I usually end up with significantly fewer fields per table and my application development time has been drastically reduced.