Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2014
    Location
    Illinois
    Posts
    6

    Thumbs up Continuously Updating Database - Question About Table Design

    Hello everyone!

    So, as the title shows... If I were to have a database that will continuously update (by the user or the application), would it be good practice to create separate tables for each individual item that would utilize constant updates? Perhaps use an atomic tables model?

    An example of this continuously updating database would be GPS coordinates. I am recording GPS coordinates in 5 minute intervals and only for 15 minutes of data will be saved. So that means only 4 entries per person utilizing the application.

    1 - current location
    2 - location 5 minutes ago
    3 - location 10 minutes ago
    4 - location 15 minutes ago

    This is just one example of how the database will always be constantly updating.

    Would it be wise to break down every item that has constant change and create a new table with that changing data?
    Last edited by Schultz; 12-03-14 at 13:05.

  2. #2
    Join Date
    May 2009
    Location
    India
    Posts
    66
    It depends on the specific application. Your current storage structure isn't clear. Are 1,2,3 and 4 rows or columns. I assume columns because you mention constant updating.

    If columns, only if you are strictly sampling all moving things and recording 15 minute snap shots at 5 minute intervals does it make sense to have the indicated structure. Even then this will not handle change in specifications - for example if at a later date it is decided to record every 3 minutes (you will need to store 7 related readings).

    A better way would be to store the 4 readings by appending rows to a table with the following structure.
    • Item code.
    • Time reading was taken
    • Starting location (1 - current reading) repeats for this group of 4 readings)
    • Current location. (Same as starting location for the first reading in the group)
    • Time of original reading


    Eg: of 4 readings:
    ITEM_XXX
    13-DEC-14 19:05
    LOC_01
    LOC_01
    13-DEC-14 19:05

    ITEM_XXX
    13-DEC-14 19:10
    LOC_01
    LOC_02
    13-DEC-14 19:05

    ITEM_XXX
    13-DEC-14 19:15
    LOC_01
    LOC_03h
    13-DEC-14 19:05

    ITEM_XXX
    13-DEC-14 19:20
    LOC_01
    LOC_04
    13-DEC-14 19:05

    and so on.

    Just keep appending to this table instead of constantly updating the table. This way you can keep all items in one table with little overheads,

    If you want to know where the thing is presently, just read in descending order of the date

    OK

Posting Permissions

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