Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    3

    Unanswered: How to make a historical data?

    Hi all

    This is my first post, be gentle
    How can i make a historical data?
    I have n fields and i want to keep a historical of liters for each day of each field.
    I have make something like this:

    Code:
    CREATE TYPE history AS OBJECT(
    date DATE,
    liters FLOAT);
    
    CREATE TABLE fields(
    field_id INTEGER PRIMARY KEY,
    hist HISTORY);
    
    INSERT INTO fields VALUES (1,  ROW(now(), 2));
    This is correct?
    How can access the liters data inside the type?
    How can i add more rows?

    Many thanks
    Last edited by nellaf1; 06-01-12 at 04:35.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not simplify it and make your table called say Readings

    table Readings
    DateTaken Date PK
    Value float

    if you need more than one reading per day then you'd need to rethink the PK
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2012
    Posts
    3
    I think that my code was better since it keeps all info for each field_id
    The output that i want is:

    Code:
    1 - (20-05-2012, 2)
        (21-05-2012, 3)
        (22-05-2012, 6)
    
    
    instead
    
    
    1 - (20-05-2012, 2)
    1 - (21-05-2012, 3)
    1 - (22-05-2012, 6)
    I want to know if i making this right.
    But how can i get the field liters since it's a type?
    And how can i add more rows to the ame field?

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by nellaf1 View Post
    I think that my code was better since it keeps all info for each field_id
    ?
    why would you need field_ID at all, if you take a sample per day then the sample date is a good enough candidate PK, making field_id irrelevant

    Im not certain what other role field_id could have

    you cannot get
    Code:
    1 - (20-05-2012, 2)
    1 - (21-05-2012, 3)
    1 - (22-05-2012, 6)
    as the field_id is a unique number

    unless effectively you are storing sampels from , say, multiple sources
    Code:
    table Sources
    ID integer PK autonumber
    description varchar
    
    table Readings
    SourceID Integer PK ' references Sources.ID
    DateTaken Date PK
    Value float
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2012
    Posts
    3
    Quote Originally Posted by healdem View Post
    ?
    why would you need field_ID at all, if you take a sample per day then the sample date is a good enough candidate PK, making field_id irrelevant

    Im not certain what other role field_id could have

    you cannot get
    Code:
    1 - (20-05-2012, 2)
    1 - (21-05-2012, 3)
    1 - (22-05-2012, 6)
    as the field_id is a unique number

    unless effectively you are storing sampels from , say, multiple sources
    Code:
    table Sources
    ID integer PK autonumber
    description varchar
    
    table Readings
    SourceID Integer PK ' references Sources.ID
    DateTaken Date PK
    Value float
    Yes, that's work but that it will not make excessive accesses?
    If i had a object "history" in each row as i said, i only will access that history and it will not access all table as you said.

Posting Permissions

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