CODEWITHSUNDEEP
databasedatabase-design
Guilherme Coutinho
Guilherme Coutinho

Reputation: 415

How do I design a database to store changes over time?

This database will store a list of children. But the problem is, they will have their weight measured once a day. How can I store the changes so I can easily query their actual weight and the weight variation over one day, one week and one month?

Upvotes: 3

Views: 2044

Answers (5)

warren
warren

Reputation: 33435

I'd think something like the following:

table kid
    int pkey(id)
    text name

table weight
    date when
    int kidid fkey(kid.id)
    int weight
    int pkey(id)

Upvotes: 5

lakshmanaraj
lakshmanaraj

Reputation: 4175

Child Table

ID (Generated) (PK)

text Name

float InitialWeight

WeightTable

ID (Generated) (PK)

date Date

float ChangeInWeight

ChildID (FK Child.ID)

Store only when ChangeInWeight <> 0.

Query Sum(ChangeInWeight) with date range from WeightTable relationship to find variation and Query IntitalWeight + Variation as above to Actualweight.

Upvotes: 0

Robert Gould
Robert Gould

Reputation: 69825

You might also want to setup a few views (basically stored select operations) that show only the current weight, and or views for other common queries, thus isolating implementation from the users.

Now if this were me, and I had a huge amount of children to keep track of, I'd probably keep a cache table with results for frequent queries, but it's probably an overkill.

Upvotes: 1

George Stocker
George Stocker

Reputation: 57872

You need an one to many relationship between a 'child' table and a 'weight' table.

Child Table
-----------
ID (Generated) (PK)
Name 


WeightTable
-----------
ID (Generated) (PK)
Date
Weight
ChildID (FK)

It's late here, and I think I'm making it more complicated than I need to; and I'm reasonably sure you could just do a One-To-Many between Child and Weight. Every time you weigh the child, make a new entry.

Upvotes: 2

AJ.
AJ.

Reputation: 248

Two tables. The first, say children, should have an id column and information about each child like name, age, etc. The second should be called something like childrenweights. Each row contains three things. The id of the child from the children table, the weight, and the time the measurement was taken. Then you can use sql to query the children_weights table to select a range of weights for a child, say starting at January 1st and ending at January 8th and do whatever with them. Or you can even have the sql query return just the average or sum of that.

Upvotes: 0

Related Questions