a question on database design - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

a question on database design

a question on database design# Database - 数据库

S*d2008-08-07 07:08

1 楼

一个relational database里，任何一个table里都要track 某些field的history。
this database is read only for users and will be loaded weekly from a
snapshot of data source.
在DESIGN阶段的问题哈，这个DATABASE还不存在
现在我有两个方案。
一，不拆开任何一个ENTITY， still both current data and historical data in
the same table, but add date fields to indicate when the row of data is
valid.
二，拆开ENTITY为二，一个TABLE放那些不要求KEEP HISTORY的FIELDS，一个TABLE放那
些要求KEEP HISTORY的FIELDS
大家帮我看看，也许还有方案三，方案四

j*n2008-08-07 07:08

2 楼

plan 3:
keep the original table as it is; create a history table for history fields
with additional datetime field for tracking; put a trigger on fields that
you need track on original table which fires when data changes.
therefore the original table/design remains no change.
since data feed happens weekly by nightly batch process (I assume), the
performance impact on trigger is mineral.

S*d2008-08-07 07:08

3 楼

Happy to see your reply, especially while seeing the Olympics.
It is going to be loaded while no one is able to access the database, I am
not too comfortable with triggers, don't know how expensive they are. Just
have to give it a try, I think.
I think your plan is good as far as keeping the original schema. While
history data fields might be added, at least the original schema won't need
to be changed, I can just add some more triggers to the non-history table
and alter the history table's stru

【在 j*****n 的大作中提到】

: plan 3:
: keep the original table as it is; create a history table for history fields
: with additional datetime field for tracking; put a trigger on fields that
: you need track on original table which fires when data changes.
: therefore the original table/design remains no change.
: since data feed happens weekly by nightly batch process (I assume), the
: performance impact on trigger is mineral.

S*d2008-08-07 07:08

4 楼

Does anyone with data warehouse exp have any input?
By the way, I like Yao Ming's English. =)

【在 S*********d 的大作中提到】

: Happy to see your reply, especially while seeing the Olympics.
: It is going to be loaded while no one is able to access the database, I am
: not too comfortable with triggers, don't know how expensive they are. Just
: have to give it a try, I think.
: I think your plan is good as far as keeping the original schema. While
: history data fields might be added, at least the original schema won't need
: to be changed, I can just add some more triggers to the non-history table
: and alter the history table's stru

j*n2008-08-07 07:08

5 楼

Trigger is the one of the easiest solutions for my solution 3. You can also
use stage table to store the new feeds; come out a batch process to have old
data to the history table; then get the new data into the final destination
... that's so called "delta", which is the common use for data warehouse.
btw, I like Yao's English too, hehe :)

S*d2008-08-07 07:08

6 楼

I actually did a search on tracking history data on the internet, there is a
book many people online recommend to read, which is Time oriented database
application in SQL. I should read that book.
Thank you very much for your input. Now I know what to serch on... "Delta"

also
old
destination

【在 j*****n 的大作中提到】

: Trigger is the one of the easiest solutions for my solution 3. You can also
: use stage table to store the new feeds; come out a batch process to have old
: data to the history table; then get the new data into the final destination
: ... that's so called "delta", which is the common use for data warehouse.
: btw, I like Yao's English too, hehe :)