sheardben — 2010-12-03T15:22:59-05:00 — #1
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!
cups — 2010-12-03T15:44:51-05:00 — #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"
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
sheardben — 2010-12-03T16:13:10-05:00 — #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)
... (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:
tripid, name, otherinfo, time_interval
5, Trip name, something else, 00.05
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..
system — 2010-12-03T16:42:42-05:00 — #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.
anthonysterling — 2010-12-03T16:51:21-05:00 — #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...
cups — 2010-12-03T17:02:49-05:00 — #6
Can you explain what that is?
thruska — 2010-12-04T13:23:37-05:00 — #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.