CODEWITHSUNDEEP
databasedatabase-design
Glebka
Glebka

Reputation: 1668

Database structure for time span

I have a peoples, everybody have:firstname,lastName. And everybody have parameters: [car count, money count, cow count...] Sometimes this parameters are changing. I want have the past parameter counts and present parameter counts for statistic. How i must build my db structure?

My version:

Upvotes: 0

Views: 119

Answers (1)

longbkit
longbkit

Reputation: 1298

This is kind of history log pattern.

[PeopleID(PK): LastName, FirstName, DayOfBirth, Adress, CarCount, MoneyCount, CowCount...] [ChangesID(PK): PeopleID(FK), ColumnId, OldValue, ChangesDate]

ColumnId will be your own definition, such as ColumnId = 1 for CarCount, ColumnId = 2 for MoneyCount, Column Id = 3 for CowCount

My idea is for People you should not create a table with just the people First & Last Name, DayOfBirth and others should be included in this table (People) as well. And for the parameters, CarCount, MoneyCount or CowCount will always store the current value. Anytime there is a change of CarCount from a to b, then update corresponding row in People table, set CarCount to b, and create a new record in Changes table in which ColumnId = 1 and OldValue = a. Similar for other parameters.

This way, you can always get the newest parameter for each person quickly by the information in table People. And in case user want to know the previous values for each parameter then it can be done by query the Changes table order by ChangesDate descendingly.

Hope this helps.

Upvotes: 1

Related Questions