如何设计数据库来存储随时间的变化？

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?

该数据库将存储子列表。但问题是,他们每天会测量一次体重。如何存储更改,以便我可以轻松查询一天,一周和一个月的实际重量和重量变化?

5 个解决方案

#1

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)

#2

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.

现在已经很晚了,而且我认为我让它变得比我需要的更复杂;而且我有理由相信你可以在孩子和体重之间做一对多。每次你给孩子称体重,都要重新入门。

#3

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.

现在,如果这是我,并且我有大量的孩子要跟踪,我可能会保留一个缓存表,其中包含频繁查询的结果,但这可能是一种过度杀伤力。

#4

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.

两张桌子。第一个,比如孩子,应该有一个id列和关于每个孩子的信息,如姓名,年龄等。第二个应该被称为像儿童权重。每行包含三件事。来自儿童表的孩子的身份,体重和测量的时间。然后你可以使用sql来查询children_weights表,为孩子选择一系列的权重,比如从1月1日开始到1月8日结束,并对它们做任何事情。或者你甚至可以让sql查询返回它的平均值或总和。

#5

Child Table

ID (Generated) (PK)

ID(生成)(PK)

text Name

float InitialWeight

WeightTable

ID (Generated) (PK)

ID(生成)(PK)

date Date

float ChangeInWeight

ChildID (FK Child.ID)

ChildID(FK Child.ID)

---

Store only when ChangeInWeight <> 0.

仅在ChangeInWeight <> 0时存储。

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

查询总和(ChangeInWeight),日期范围从WeightTable关系到查找变量,查询IntitalWeight + Variation如上,到Actualweight。