SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    best way to store this data

    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. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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...
    Code:
    trip.id
    trip.date
    
    reading.value
    reading.trip_id
    reading.sensor_id
    
    sensor.id
    sensor.name
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    time_interval
    Can you explain what that is?

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2010
    Location
    Arizona
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Thomas Hruska

    Single Sign-On Server/Client - The PHP login system that rocks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •