存储日期及其更改和电子邮件的历史-我的数据库设计有什么问题吗？

Question

背景

我正在设计一些表，允许将日期分配给对象，以便可以跟踪该对象的进度，以及存储与这些合同及其日期相关的电子邮件的功能。例如，让我们假设对象是一个契约(一个要完成的契约任务)。

这意味着我们会有一个合同表

现在，对于此合同，我们可以有日期字段，如获取日期、开始日期、审查日期、估计完成日期、完成日期等。现在，大多数合同将包含相同的列，但我们不知道将来是否会增加更多的列，或者随着需求的频繁变化而使用更少的列。

因此，我想把日期从合同表中分离出来。此ContractDate表中的每个日期都有一个FieldHeading表的外键，该外键描述该日期是合同的哪个方面。最后，有一个名为AuditTrail类型的表，名为ContractDateHistory，它将跟踪日期中的更改。

这样我们也可以有选择地选择合同的日期。一份合同可能有4个日期，另一个合同可能有6个日期。

这些合同可以向人们发送电子邮件，告知他们这些日期是如何进行的，或者日期是否发生了变化。由于多个合同可以包含在一封电子邮件中，所以我有一个ContractsForEmail表，它包含电子邮件中包含的每个合同的电子邮件ID。

发送给用户的每一封电子邮件都会输入一行，如果用户必须对电子邮件采取行动(取决于电子邮件的类型)，needsResolution就在那里，如果用户已采取行动，程序将检查用户是否在一段时间后采取了行动，如果没有，程序将发送一封提醒邮件(该邮件可能也会出现在此表中)。

模板可以有从模板发送的电子邮件发送给的用户列表(将为分配给模板邮件列表的每个用户发送一封电子邮件，其他用户也可以发送其他邮件，但如果发送电子邮件失败，则保留在这里。

My问题

关于我的设计是否合理，我有很多疑问，但具体而言，我想知道，我对合同和contractDates做了什么？

与其将合同和日期分开，不如将日期列添加到Contracts表中吗？

如果它们被使用，那么就很好，如果它们不用于特定的合同，那么它将被保留为空。

这可能导致契约表中的列被添加并不再使用(几乎总是NULL)。就像这样：

AddedDateInFuture的存在是为了说明将来可能会添加一个列，但实际上它并不存在。

• What是这两个选项之间的权衡吗？
• Is，我在电子邮件表格上遗漏了什么吗？这种设计会有用吗？

目前，该系统将满足不到200个用户，但我希望它是一个未来的小证明，如果更多地使用它。

最后请注意，一些数据库表(例如通常的“用户”表)已经被忽略了。

Edit - SELECT语句

要获得合同和合同日期的信息，应该是这样的。

SET @ContractID = 1;

SELECT * FROM Contracts C
JOIN ContractDates CD ON C.ID = CD.Contract_ID
JOIN FieldHeading FH ON CD.FieldHeading_ID = FH.ID
WHERE C.ID = @ContractID

这将返回多行，所有行都具有相同的合同信息，但每行中的日期字段和该日期的列标题不同(取自FieldHeading)。

如果我想查看所有批次的所有日期，我必须在ContractDate上做一个ContractDate

SELECT * FROM Contracts C
LEFT JOIN ContractDates CD ON C.ID = CD.Contract_ID
LEFT JOIN FieldHeading FH ON CD.FieldHeading_ID = FH.ID

查看日期的历史不会经常发生，但这是必须的。让我们说，我们想看看日期是什么样的时候，一个特定的电子邮件被发送。

SET @emailID = 1;

SELECT E.ID, E.DateSent, C.ID, C.Name, C.CreationDate, C.Comments, CDH.ID, 
CDH.ContractDate_ID, MAX(CDH.TimeStamp) AS 'TimeStamp', FH.Name, FH.HeadingOrder FROM Email E

JOIN ContractsForEmail CFE ON E.ID = CFE.Email_ID
JOIN Contracts C ON CFE.Contracts_ID = C.ID
-- We join the history to see what the dates were at the time of the sent email.
LEFT JOIN ContractDateHistory CDH ON C.ID = CDH.ContractDate_Contract_ID
LEFT JOIN FieldHeading FH ON CDH.ContractDate_FieldHeading_ID = FH.ID
WHERE E.ID = @emailID AND (CDH.TimeStamp <= E.DateSent OR CDH.ID IS NULL)
GROUP BY C.ID, CDH.ContractDate_FieldHeading_ID

这将返回在电子邮件发送时填写的每个日期的行。如果合同包含在电子邮件中，但没有填写日期，则合同仍应显示在查询中。

If你想玩

我将分享一些快速而肮脏的创建和插入语句，以可视化SELECT语句是如何工作的。请记住，我没有指定任何外键或任何这只是演示。

-- CREATE Statements --

CREATE TABLE contracts (ID INTEGER, Name VARCHAR(100), CreationDate DATETIME,
 CreatedBy INTEGER, Comments VARCHAR(100), PRIMARY KEY(ID));

CREATE TABLE contractsforemail (ID INTEGER, Contracts_ID INTEGER, Email_ID INTEGER, Email_EmailTemplate_ID INTEGER, PRIMARY KEY(ID));

Create table email (ID INTEGER, EmailTemplate_ID INTEGER, DateSent DATETIME, 
NeedsResolution BOOL, Resolved BOOL, ReminderSent BOOL, userID INTEGER, PRIMARY KEY(ID));
CREATE TABLE fieldheading(ID integer, Name VARCHAR(100),  HeadingOrder INTEGER, PRIMARY KEY(ID));
CREATE TABLE contractdates (ID INTEGER, Contract_ID INTEGER, FieldHeading_ID INTEGER, DateValue DATETIME, PRIMARY KEY(ID));
CREATE TABLE ContractDateHistory (ID INTEGER, ContractDate_FieldHeading_ID INTEGER, ContractDate_Contract_ID INTEGER, ContractDate_ID INTEGER, TimeStamp DATETIME, UserID INTEGER, PRIMARY KEY(ID));

-- INSERTs --

--Insert Four Contracts
INSERT INTO Contracts(ID, Name, CreationDate, CreatedBy, Comments) 
VALUES (1, 'Contract 1', '2019-06-01', 4, 'First Contract');

INSERT INTO Contracts(ID, Name, CreationDate, CreatedBy, Comments) 
VALUES (2, 'Contract 2', '2019-06-03', 4, 'Second Contract');

INSERT INTO Contracts(ID, Name, CreationDate, CreatedBy, Comments) 
VALUES (3, 'Contract 3', '2019-06-07', 4, 'Third Contract');

INSERT INTO Contracts(ID, Name, CreationDate, CreatedBy, Comments) 
VALUES (4, 'Contract 4', '2019-06-15', 4, 'Fourth Contract');


--Insert the fields for a Contract
INSERT INTO fieldheading (ID, Name,  HeadingOrder) 
VALUES (1, 'Obtained Date', 1), (2, 'Start Date', 2), ( 3,'Review Date' , 3), ( 4, 'Estimated Finish Date', 4), (5 ,'Finish Date' , 5);

-- Dates For Contract 1
INSERT INTO ContractDates (ID, Contract_ID, FieldHeading_ID, DateValue) 
VALUES (1, 1, 1, '2019-06-01'), (2, 1, 2, '2019-06-05'), (3, 1, 3, '2019-06-08'), (4, 1, 4, '2019-06-12'), (5, 1, 5, '2019-06-13');

-- Dates For Contract 2
INSERT INTO ContractDates (ID, Contract_ID, FieldHeading_ID, DateValue) 
VALUES (6,2,1, '2019-06-03'), (7,2,2,'2019-06-15');


-- History For Contract 1
INSERT INTO ContractDateHistory (ID, ContractDate_FieldHeading_ID, ContractDate_Contract_ID, ContractDate_ID, TimeStamp, UserID) 
VALUES (1,1,1,1,'2019-06-01',1), (2,2,1,2,'2019-06-02', 4), (3,3,1,3, '2019-06-02', 4), (4,4,1,4,'2019-06-09', 4), (5,4,1,4, '2019-06-10', 4), (6,5,1,5, '2019-06-13', 1), (7, 4, 1, 4, '2019-06-12', 4);

-- History For Contract 2
INSERT INTO ContractDateHistory (ID, ContractDate_FieldHeading_ID, ContractDate_Contract_ID, ContractDate_ID, TimeStamp, UserID) 
VALUES (8,1,2,6, '2019-06-03', 1), (9,2,2,7,'2019-06-12',4);


--Insert an email sent on 2019-06-10
INSERT INTO Email(ID, EmailTemplate_ID, DateSent, NeedsResolution, Resolved,
ReminderSent, UserID) 
VALUES (1, 1, '2019-06-10', 0,1,0,4);



--Insert that the email was pertaining to Contract 1 and Contract 2
INSERT INTO ContractsForEmail(ID, Contracts_ID, Email_ID, Email_EmailTemplate_ID)
VALUES (1, 1, 1, 1), (2,2,1,1);

Answer 1

由于您已经仔细考虑了这些表和查询，所以您已经有了一个良好的开端。

可能会让你绊倒的事情：

• 您只有PRIMARY KEYs；JOINs很可能需要至少一个其他列的索引。
• fieldheading闻起来像是显示信息；这通常最好留给显示代码，而不是数据库。
• 一个“历史”的变化是困难和混乱的，但这是一个要求，你将不得不接受。
• 假设焦点是当前的数据(历史是次要的)，明确区分“当前”表(S)和“历史”表(S)。
• 如果您希望在任何表中有数百万行，我预测您将再次出现性能问题。(但是模式和查询从现在到现在都会发生几次变化，所以我今天不能给您提供具体的建议。)