如何在 SQL 数据库中存储业务活动？

Question

目标是存储插入、更新和删除业务记录等活动。

我正在考虑的一种解决方案是对每个要跟踪的记录使用一个表。这是一个简化的示例：

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)
)

此解决方案似乎适合获取给定小部件或 dingbat 记录 id 的所有活动，但是对于获取所有活动然后尝试确定它们引用的记录似乎不太好。

也就是说，在此示例中，所有帐户名称和时间戳都存储在单独的表中，因此可以轻松创建专注于用户和时间间隔的报告，而无需知道具体的活动是什么。

但是，如果您确实想特别按类型报告活动，则此解决方案需要确定常规活动表引用的活动类型。

我可以将所有活动类型放在一张表中，但是 ID 无法受外键约束，而是可以将表名称用作 ID，这将导致我使用动态查询。

请注意，在示例中，DingBatActivity 有一个可选的按钮 ID。如果按钮名称在添加到 dingbat 后进行了编辑，则活动将能够引用该按钮并知道其名称，因此，如果报告按名称列出了 dingbat 和按钮的所有活动，则按钮名称会更改将自动反映在活动描述中。

寻找一些其他想法以及这些想法如何在编程工作、数据完整性、性能和报告灵活性之间进行折衷。

Answer 1

我通常构建此问题解决方案的方式类似于对象中的继承。如果您在某些实体上发生了“活动”并且您想要跟踪这些活动，那么所涉及的实体几乎肯定有一些共同点。这是你的基桌。从那里，您可以在基表之外创建子表来跟踪特定于该子类型的内容。例如，您可能有：

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),
)

如果您想要为受其影响的对象类型添加类型代码，则可以将类型代码添加到基本活动，或者您可以通过在子表中查找是否存在来确定该类型代码。

不过，这里有一个大警告：确保对象/活动确实有一些共同点，这些共同点将它们联系起来，并要求您沿着这条路走下去。您不想在同一个表中存储脱节、不相关的数据。例如，您可以使用此方法创建一个表来保存银行帐户交易和天体事件，但这不是一个好主意。在基础层面上，他们需要有一些共同点。

另外，我假设您的所有活动都与帐户相关，这就是它位于基表中的原因。所有活动的任何共同点都放在基表中。仅与子类型相关的内容会出现在这些表中。您甚至可以深入多个级别，但不要得意忘形。对象也是如此（同样，这里的名字不好，但我不确定你实际上在处理什么）。如果所有对象都有颜色，那么您可以将其放入对象表中。如果没有，那么它将进入子表。

Answer 2

我将冒险对你真正想要实现的目标进行一些大胆的猜测。

您说您正在尝试跟踪“商店活动”，我假设您有以下活动：
购买新商品
出售物品
注销项目
雇用员工
支付员工工资
消防员
更新员工记录

好的，对于这些活动，您需要几个不同的表：一张用于库存，一张用于部门，一张用于员工

库存表可能包含以下信息：

inventory:
  item_id (pk)
  description (varchar)
  number_in_stock (number)
  cost_wholesale (number)
  retail_price (number)
  dept_id (fk)

department:
  dept_id (pk)
  description (varchar)

employee
  emp_id (pk)
  first_name (varchar)
  last_name (varchar)
  salary (number)
  hire_date (date)
  fire_date (date)

因此，当您购买新商品时，您将更新库存表中的 number_in_stock ，或者如果它是您以前从未拥有过的商品，则创建一个新行。当您出售商品时，您会减少该商品的 库存数量（当您注销商品时也是如此）。

当您雇用新员工时，您可以将他们的记录添加到员工表中。当你付钱给他们时，你从工资栏中获取他们的工资。当你解雇他们时，你填写该栏作为他们的记录（并停止向他们付款）。

---

在所有这一切中，所做并不是由数据库完成的。 SQL 应该用于跟踪信息。编写执行这些更新的程序（更新发票记录中的所有项目的新发票程序）是很好的。但您不需要一张桌子来做事情。事实上，表格不能做任何事情。

在设计数据库时，您需要问的问题不是“我需要做什么？”这是“我需要跟踪哪些信息？”

Answer 3

基于对问题的不同解释的新答案。

您只是想保留已发生的事情的清单吗？如果您只需要过去事件的有序列表，则只需要 1 个表：

action_list
  action_list_id (pk)
  action_desc (varchar)

event_log:
  event_log_id (pk)
  event_time (timestamp)
  action_list_id (fk)
  new_action_added (fk)
  action_details_or_description (varchar)

在这种情况下，action_list 将类似于：

1   'WidgetRotated'
2   'WidgetFlipped'
3   'DingBatPushed'
4   'AddNewAction'
5   'DeleteExistingAction'

event_log 将是发生的活动以及时间的列表。您的操作之一是“添加新操作”，并且每当采取的操作是“添加新操作”时，都需要在事件表中填写“new_action_added”列。

您可以创建更新、删除、添加等操作。

编辑：
我将 action_details_or_description 列添加到事件中。通过这种方式，您可以提供有关操作的更多信息。例如，如果您有“产品更改颜色”操作，则新颜色的描述可以是“红色”。

更广泛地说，您需要提前考虑并规划出您将要采取的所有不同类型的操作，以便您可以以能够准确包含您想要的数据的方式设置表。放入其中。

Answer 4

SQL日志怎么样？

Answer 5

上次我需要数据库事务记录器时，我使用了 数据库中的触发器，这样数据库将不只是更新记录，而是将新记录插入到日志表中。这种技术意味着我需要一个额外的表来保存数据库中每个表的日志，并且日志表有一个带有时间戳的附加列。如果需要，使用此技术您甚至可以存储记录的更新前和更新后状态。