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.
目前，我们可以访问产品的数据库设计。它在SQL Server上运行，我们定期针对产品表运行自己的SQL查询，以生成我们自己的表。然后，我们将表格链接到Excel中的数据透视表以生成图表。所以我们熟悉数据库设计和SQL。但是，我们仍然坚持如何最好地解决这个问题。
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).
该产品记录会员的会员购买情况以及他们的开始和到期日期。因此，我们可以回过头来确定会员在任何时间点的类型和状态。例如，如果他们在2007年1月1日购买了初级会员并且在2007年12月31日到期，然后他们在2008年6月1日购买了学生会员资格，我们可以看到他们的状态从活动状态变为非活动状态到活动状态（1月1日）分别于2008年1月和2008年6月1日），他们的类型从大三到学生（2008年6月1日）。
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).
基本上我们希望将成员的类型和状态属性转换为时间属性或有效性a-la Fowler（或其他随时间变化的事物）。
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.
我们的问题（最后:) - 鉴于上述情况：您建议我们使用哪种数据库表设计来保存此成员信息。我想它会有一个MemberID列，所以我们可以键入现有的Member表。它还需要存储成员的状态和类型以及他们所持有的日期范围。我们希望能够轻松地针对此表编写查询，以确定在给定时间点我们拥有的每种类型和状态的成员数量。
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.
更新2009-08-25：已被侧面跟踪，尚未有机会尝试提出的解决方案。希望尽快这样做，并根据结果选择答案。
4 个解决方案
#1
7
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.
鉴于您的系统已经编写并且到位，解决此问题的最简单方法（以及影响现有数据库/代码的最简单方法）是添加包含MemberID，status，type和date列的成员资格历史记录表。然后将UPDATE和INSERT触发器添加到主成员表。当这些触发器触发时，您将成员的新值（以及状态更改的日期）写入成员历史记录表中。然后，您可以只查询此表以获取每个成员的历史记录。
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. :)
我会为你写这个免费会员资格。 :)
#2
2
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.
我不能推荐你阅读Joe Celko的“Sql for smarties - 高级sql编程”。他有一整章关于时态数据库设计以及如何（有效地）运行时间投影，选择和时间连接查询。我甚至不会试图解释他在这篇文章的章节中所说的内容。
#3
1
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.
然后，我会将报告从报告数据库中删除。使星型模式与数据透视表的功能完全相同是非常容易的。如有必要，我会在报告数据库前面找到某种OLAP工具。
This is a lot of work, but it would pay off over time.
这是很多工作，但它会随着时间的推移而得到回报。
#4
0
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.
我会将会员信息放在它自己的表中，包含开始日期和结束日期。将客户保持在单独的表中。如果您一直需要“当前”会员信息，这是一种痛苦，但有很多方法可以通过查询或触发器解决这个问题。
#1
7
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.
鉴于您的系统已经编写并且到位，解决此问题的最简单方法（以及影响现有数据库/代码的最简单方法）是添加包含MemberID，status，type和date列的成员资格历史记录表。然后将UPDATE和INSERT触发器添加到主成员表。当这些触发器触发时，您将成员的新值（以及状态更改的日期）写入成员历史记录表中。然后，您可以只查询此表以获取每个成员的历史记录。
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. :)
我会为你写这个免费会员资格。 :)
#2
2
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.
我不能推荐你阅读Joe Celko的“Sql for smarties - 高级sql编程”。他有一整章关于时态数据库设计以及如何（有效地）运行时间投影，选择和时间连接查询。我甚至不会试图解释他在这篇文章的章节中所说的内容。
#3
1
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.
然后，我会将报告从报告数据库中删除。使星型模式与数据透视表的功能完全相同是非常容易的。如有必要，我会在报告数据库前面找到某种OLAP工具。
This is a lot of work, but it would pay off over time.
这是很多工作，但它会随着时间的推移而得到回报。
#4
0
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.
我会将会员信息放在它自己的表中，包含开始日期和结束日期。将客户保持在单独的表中。如果您一直需要“当前”会员信息，这是一种痛苦，但有很多方法可以通过查询或触发器解决这个问题。