我们的目标是到商店的活动，如插入，更新和删除业务记录。

The goal is to store activities such as inserting, updating, and deleting business records.

一个解决方案，我在考虑是使用一个表每个被跟踪记录。下面是一个简单的例子：

One solution I'm considering is to use one table per record to be tracked. Here is a simplified example:

CREATE TABLE ActivityTypes
(
    TypeId              int IDENTITY(1,1)       NOT NULL,
    TypeName            nvarchar(50)            NOT NULL,

    CONSTRAINT PK_ActivityTypes          PRIMARY KEY (TypeId),
    CONSTRAINT UK_ActivityTypes          UNIQUE (TypeName)
)

INSERT INTO ActivityTypes (TypeName) VALUES ('WidgetRotated');
INSERT INTO ActivityTypes (TypeName) VALUES ('WidgetFlipped');
INSERT INTO ActivityTypes (TypeName) VALUES ('DingBatPushed');
INSERT INTO ActivityTypes (TypeName) VALUES ('ButtonAddedToDingBat');

CREATE TABLE Activities
(
    ActivityId          int IDENTITY(1,1)       NOT NULL,
    TypeId              int                     NOT NULL,
    AccountId           int                     NOT NULL,
    TimeStamp           datetime                NOT NULL,

    CONSTRAINT PK_Activities                      PRIMARY KEY (ActivityId),
    CONSTRAINT FK_Activities_ActivityTypes        FOREIGN KEY (TypeId)
                                                  REFERENCES ActivityTypes (TypeId),
    CONSTRAINT FK_Activities_Accounts             FOREIGN KEY (AccountId)
                                                  REFERENCES Accounts (AccountId)
)

CREATE TABLE WidgetActivities
(
    ActivityId          int                     NOT NULL,
    WidgetId            int                     NOT NULL,

    CONSTRAINT PK_WidgetActivities                  PRIMARY KEY (ActivityId),
    CONSTRAINT FK_WidgetActivities_Activities       FOREIGN KEY (ActivityId)
                                                    REFERENCES Activities (ActivityId),
    CONSTRAINT FK_WidgetActivities_Widgets          FOREIGN KEY (WidgetId)
                                                    REFERENCES Widgets (WidgetId)
)

CREATE TABLE DingBatActivities
(
    ActivityId          int                     NOT NULL,
    DingBatId           int                     NOT NULL,
    ButtonId            int,

    CONSTRAINT PK_DingBatActivities                  PRIMARY KEY (ActivityId),
    CONSTRAINT FK_DingBatActivities_Activities       FOREIGN KEY (ActivityId)
                                                     REFERENCES Activities (ActivityId),
    CONSTRAINT FK_DingBatActivities_DingBats         FOREIGN KEY (DingBatId)
                                                     REFERENCES DingBats (DingBatId)
    CONSTRAINT FK_DingBatActivities_Buttons          FOREIGN KEY (ButtonId)
                                                     REFERENCES Buttons (ButtonId)
)

该解决方案似乎有利于获取给定的一个小部件或白带记录ID的所有活动，但它似乎没有对取所有的活动，然后试图确定它们指向的战绩这么好。

This solution seems good for fetching all activities given a widget or dingbat record id, however it doesn't seem so good for fetching all activities and then trying to determine to which record they refer.

即，在本实施例中，所有的帐户名和时间戳存储在一个单独的表，所以可以很容易地创建侧重于用户报告和集中的时间间隔，而无需知道该活度是特别什么。

That is, in this example, all the account names and timestamps are stored in a separate table, so it's easy to create reports focused on users and focused on time intervals without the need to know what the activity is in particular.

然而，如果你确实想在特定类型的活动情况，该解决方案将需要确定哪个活动类型的一般活动表指向。

However, if you did want to report on the activities by type in particular, this solution would require determining to which type of activity the general activity table refers.

我可以把我所有的活动类型在一个表中，但是ID的将不能被一个外键，而不是表名可能被用来作为一个ID，这将导致我使用动态查询的限制。

I could put all my activity types in one table, however the ID's would not be able to be constrained by a foreign key, instead the table name might be used as an id, which would lead me to use dynamic queries.

请注意在一个DingBatActivity有一个可选按钮的ID的例子。如果按钮的名字要被添加到丁巴特后已编辑，该活动将能够参照按钮，知道它的名字，因此，如果报告中列出的所有活动丁巴特和按钮的名字，按钮名称变更将自动地反映在活动描述

Note in the example that a DingBatActivity has an optional button Id. If the button name were to have been edited after being added to the dingbat, the activity would be able to refer to the button and know its name, so if a report listed all activities by dingbat and by button by name, the button name change would automatically be reflected in the activity description.

寻找一些其他的想法，以及这些想法的编程工作，数据的完整性，性能和报告的灵活性之间的妥协。

Looking for some other ideas and how those ideas compromise between programming effort, data integrity, performance, and reporting flexibility.

推荐答案

的方式，我通常会构建一个解决这个问题类似于继承的对象。如果你有活动正在发生的某些实体，并要跟踪这些活动则涉及到几乎可以肯定的实体有一些共同点。这是你的基表。从那里，你可以创建子表落基表来跟踪特定于该子类的东西。例如，你可能有：

The way that I usually architect a solution to this problem is similar to inheritance in objects. If you have "activities" that are taking place on certain entities and you want to track those activities then the entities involved almost certainly have something in common. There's your base table. From there you can create subtables off of the base table to track things specific to that subtype. For example, you might have:

CREATE TABLE Objects   -- Bad table name, should be more specific
(
     object_id     INT          NOT NULL,
     name          VARCHAR(20)  NOT NULL,
     CONSTRAINT PK_Application_Objects PRIMARY KEY CLUSTERED (application_id)
)

CREATE TABLE Widgets
(
     object_id     INT           NOT NULL,
     height        DECIMAL(5, 2) NOT NULL,
     width         DECIMAL(5, 2) NOT NULL,
     CONSTRAINT PK_Widgets PRIMARY KEY CLUSTERED (object_id),
     CONSTRAINT FK_Widgets_Objects
     FOREIGN KEY (object_id) REFERENCES Objects (object_id)
)

CREATE TABLE Dingbats
(
     object_id     INT           NOT NULL,
     label         VARCHAR(50)   NOT NULL,
     CONSTRAINT PK_Dingbats PRIMARY KEY CLUSTERED (object_id),
     CONSTRAINT FK_Dingbats_Objects
     FOREIGN KEY (object_id) REFERENCES Objects (object_id)
)

现在你的活动：

CREATE TABLE Object_Activities
(
     activity_id     INT          NOT NULL,
     object_id       INT          NOT NULL,
     activity_type   INT          NOT NULL,
     activity_time   DATETIME     NOT NULL,
     account_id      INT          NOT NULL,
     CONSTRAINT PK_Object_Activities PRIMARY KEY CLUSTERED (activity_id),
     CONSTRAINT FK_Object_Activities_Objects
     FOREIGN KEY (object_id) REFERENCES Objects (object_id),
     CONSTRAINT FK_Object_Activities_Activity_Types
     FOREIGN KEY (activity_type) REFERENCES Activity_Types (activity_type),
)

CREATE TABLE Dingbat_Activities
(
     activity_id     INT     NOT NULL,
     button_id       INT     NOT NULL,
     CONSTRAINT PK_Dingbat_Activities PRIMARY KEY CLUSTERED (activity_id),
     CONSTRAINT FK_Dingbat_Activities_Object_Activities
     FOREIGN KEY (activity_id) REFERENCES Object_Activities (activity_id),
     CONSTRAINT FK_Dingbat_Activities_Buttons
     FOREIGN KEY (button_id) REFERENCES Object_Activities (button_id),
)

您可以添加类型code到基本活动，如果你想为对象，这是影响的类型或者你可以决定通过查找存在的一个子表。

You can add a type code to the base activity if you want to for the type of object which it is affecting or you can just determine that by looking for existence in a subtable.

下面是在大警告，虽然：确保对象/活动确实有一些共同点涉及他们，并要求你走上这条道路。你不想来存储脱节，不相关的数据在同一个表。例如，你可以用这个方法来创建一个拥有两个银行账户的交易和天体事件表，但是这不会是一个好主意。在基本水平，他们需要有共同的东西。

Here's the big caveat though: Make sure that the objects/activities really do have something in common which relates them and requires you to go down this path. You don't want to store disjointed, unrelated data in the same table. For example, you could use this method to create a table that holds both bank account transactions and celestial events, but that wouldn't be a good idea. At the base level they need to have something in common.

另外，我认为所有的活动都涉及到一个帐户，这就是为什么它在基表。任何在这些活动都进入基表。相关的只有一个亚型事情的表。你甚至可以去多层次深，但不要得意忘形。这同样适用于物体（同样，不好的名字在这里，但我不知道你居然跟处理）。如果你的对象有一个颜色，那么你可以把它放在对象表。如果没有，那么它将进入子表

Also, I assumed that all of your activities were related to an account, which is why it's in the base table. Anything in common to ALL activities goes in the base table. Things relevant to only a subtype go in those tables. You could even go multiple levels deep, but don't get carried away. The same goes for the objects (again, bad name here, but I'm not sure what you're actually dealing with). If all of your objects have a color then you can put it in the Objects table. If not, then it would go into sub tables.