数据库设计来保存随时间变化的人的信息？
我们使用第三方产品来管理我们的体育中心会员资格。我们有多种会员类型（例如初级会员、学生会员、员工会员、社区会员）和多种会员身份（例如年度会员、活跃会员、非活跃会员、暂停会员）。不幸的是，该产品仅记录会员当前的会员类型和状态。我希望能够跟踪我们的会员类型和状态随时间的变化情况。
目前我们已经获得了该产品的数据库设计。它在 SQL Server 上运行，我们定期对产品的表运行我们自己的 SQL 查询以生成我们自己的表。然后，我们将表格链接到 Excel 中的数据透视表以生成图表。所以我们熟悉数据库设计和SQL。然而，我们一直不知道如何最好地解决这个问题。
该产品记录会员的会员资格购买及其开始日期和到期日期。因此，我们可以在任何时间点回溯这些数据来确定成员的类型和状态。例如，如果他们在 2007 年 1 月 1 日购买了初级会员，并于 2007 年 12 月 31 日到期，然后他们在 2008 年 6 月 1 日购买了学生会员，我们可以看到他们的状态从活跃变为不活跃，再变为活跃（1 月 1 日）。分别是2008年1月1日和2008年6月1日），他们的类型从初级变为学生（2008年6月1日）。
本质上，我们希望将成员的类型和状态属性转换为 时间属性 或 有效性 a-la Fowler（或其他随时间变化的东西）。
我们的问题（最后:) - 考虑到上述情况：您建议我们使用什么数据库表设计来保存此成员信息。我想它会有一个 MemberID 列，这样我们就可以键入现有的 Member 表。它还需要存储成员的状态和类型以及他们被保留的日期范围。我们希望能够轻松地针对此表编写查询，以确定在给定时间点我们拥有的每种类型和状态的成员数量。
更新2009-08-25：已经偏离轨道，还没有机会尝试建议的解决方案。希望尽快这样做，并将根据结果选择答案。
We use a third-party product to manage our sports centre membership. We have several membership types (eg. junior, student, staff, community) and several membership statuses (eg. annual, active, inactive, suspended). Unfortunately the product only records a member's current membership type and status. I'd like to be able to track the way our members' type and status have changed over time.
At present, we have access to the product's database design. It runs on SQL Server and we regularly run our own SQL queries against the product's tables to produce our own tables. We then link our tables to pivot-tables in Excel to produce charts. So we're familiar with database design and SQL. However we're stuck as to how to best approach this problem.
The product records a member's membership purchases and their start and expiry dates. So we can work back through that data to determine a member's type and status at any point in time. For example, if they bought a junior membership on Jan 1, 2007 and it expired on Dec 31, 2007 and then they bought a student membership on Jun 1, 2008, we can see their status went from active to inactive to active (on Jan 1, 2008 and Jun 1, 2008, respectively) and their type went from junior to student (on Jun 1, 2008).
Essentially we'd like to turn a member's type and status properties into temporal properties or effectivities a-la Fowler (or some other thing that varies with time).
Our question (finally :) - given the above: what database table design would you recommend we use to hold this member information. I imagine it would have a column for MemberID so we can key into the existing Member table. It would also need to store a member's status and type and the date range they were held for. We'd like to be able to easily write queries against this table(s) to determine how many members of each type and status we had at a given point in time.
UPDATE 2009-08-25: Have been side-tracked and haven't had a chance to try out the proposed solutions yet. Hope to do so soon and will select an answer based on the results.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
鉴于您的系统已经编写并就位，解决此问题的最简单方法（也是对现有数据库/代码影响最小的方法）是添加一个包含 MemberID、状态、类型和日期列的成员资格历史表。然后向主成员表添加一个 UPDATE 和一个 INSERT 触发器。当这些触发器触发时，您将成员的新值（以及状态更改的日期）写入成员历史记录表中。然后，您可以查询此表来获取每个成员的历史记录。
这实现起来相当简单，并且完全不会影响现有系统。
我会为你写这篇文章以获得免费会员资格。 :)
Given that your system is already written and in place, the simplest approach to this problem (and the one that affects the existing database/code the least), is to add a membership history table that contains MemberID, status, type and date columns. Then add an UPDATE and an INSERT trigger to the main member table. When these triggers fire, you write the new values for the member (along with the date of the status change) into the member history table. You can then just query this table to get the histories for each member.
This is fairly simple to implement, and won't affect the existing system at all.
I'll write this for you for a free membership. :)
我非常推荐您阅读 Joe Celko 的“Sql for smarties - 高级 sql 编程”。他有一整章关于时态数据库设计以及如何（高效且有效地）运行时态投影、选择和时态连接查询。我什至不会试图解释他在这篇文章的章节中所说的内容，这对他来说是不公平的。
I cannot recommend you enough to read Joe Celko's "Sql for smarties - advanced sql programming". he has a whole chapter on temporal database design AND how to (effeciently and effectively) run Temporal Projection, Selection and Temporal Join queries. And I would not do him justice to even attempt to explain what he says in his chapter in this post.
我将创建一个组织成星型模式的报告数据库。成员资格维度将按时间排列，以便同一成员在不同时间点会有不同的行。这样事实表中的不同行可能涉及历史上的不同点。
然后，我将创建更新程序，用于定期从主数据库更新报告数据库，例如每周更新一次。这就是主要工作的地方。
然后，我会将报告从报告数据库中删除。让星型模式执行与数据透视表相同的操作非常容易。如果有必要，我会在报告数据库前使用某种 OLAP 工具。
这是一项繁重的工作，但随着时间的推移，它会得到回报。
I would create a reporting database that was organized into a star schema. The membership dimension would be arranged temporally, so that there would be different rows for the same member at different points in time. That way different rows in the fact table could pertain to different points in history.
Then I would create update procedures for updating the reporting database periodically, say one a week, from the main database. This is where the main work would come.
Then, I would drive the reports off the reporting database. It's pretty easy to make a star schema do the same things a pivot table does. If necessary, I'd get some kind of OLAP tool to sit in front of the reporting database.
This is a lot of work, but it would pay off over time.
我会将会员信息放入其自己的表中，并包含开始日期和结束日期。将客户保留在单独的表中。如果您始终需要“当前”会员信息，这会很痛苦，但有很多方法可以通过查询或触发器来解决这个问题。
I would put the membership info in it's own table with start and end dates. Keeping the customer in separate table. This is a pain if you need the "current" membership info all the time but there are many ways to get around that either through queries or triggers.