Storing dates and their history of changes and emailing - Does my database design pose any problems?

Question

Background

I'm designing some tables to allow dates to be assigned to an object so progress can be tracked on that object, along with functionality to store emails sent pertaining to these contracts and their dates. For example lets say that the Object is a Contract (A contracted task to be completed).

This means we would have a contract table

Now for this contract, we can have Date fields such as an obtained date, start date, review date, estimated finish date, finish date etc. Right now most contracts will contain the same columns but we don't know in the future if more columns will be added or fewer will be used as requirements change quite often.

Because of this, I figured to separate the dates from the contract table. Each date in this ContractDate table would have a foreign key to a FieldHeading table that describes what aspect of the contract the date is. And finally, a AuditTrail type table called ContractDateHistory that would keep track of changes in a Date.

This way we can also selectively choose what dates go to a contract. One contract may have 4 dates and another may have 6.

These contracts can have emails sent to people that inform them of how these dates are going or if a date has changed. Because multiple contracts can be contained in one email I have a ContractsForEmail table which contains the ID of the email of each contract that is contained in an email.

A row would be input for each email sent to a user, needsResolution is there if the user has to take action with the email (depends on the type of email) and resolved is there if the user has taken action, a program will check if the user has taken action after a set amount of time, and if not, the program will send a reminder email (which could possibly also show up in this table)

The template can have a list of users that the emails derived from the template sends to (an email will be sent for each user assigned to the mailing list of a template, additional could be sent too but users that without fail get an email sent are kept here.

My Question

I have many questions about if my design is okay but in specific I'd like to know, if what I am doing with contracts and contractDates is okay?

Instead of having the Contract and Dates separate should I just add Date columns to the Contracts table?

If they get used then good and if they don't for a specific contract then its just kept as NULL.

This could lead to columns in the contract table that get added and never used again (almost always NULL). Like so:

AddedDateInFuture is there to illustrate that a column may be added in the future, it wouldn't literally be there.

• What are the trade-offs between the two options?
• Is there anything I'm missing with the emailing tables? Would this kind of design work?

Currently the system would be catering to under 200 users but I'd like it to be built a little future-proof for if much more use it.

As a last note some database tables such as the usual "User" table has been left out.

Edit - SELECT Statements

To get information on a contract and their dates would look like this.

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

This would return multiple rows all with the same contract information but different date fields in each row and the column heading for that date (taken from FieldHeading)

If I wanted to view all dates for all lots I would have to do a LEFT JOIN on 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

Viewing the history of the dates wouldn't happen too often but is a must. Lets say we want to see what the dates were like when a specific email was sent.

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

This would return a row for each date that is filled out when the email was sent. If a Contract is included in the email but no dates are filled out, the contract should still be shown in the query.

If you want to play around

I'll share some quick and dirty CREATE and INSERT Statements I made to visualise how the SELECT statements work. Please keep in mind that I didn't specify any of the foreign keys or anything this is just to demo.

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

Since you have thought through the tables and the queries, you are off to a good start.

Things that may trip you up:

• You have only PRIMARY KEYs; JOINs are very likely to need at least one other column indexed.
• fieldheading smells like display info; that is usually best left to the display code, not the database.
• A "history" of changes is difficult and messy, but it is a requirement that you will have to live with.
• Assuming the focus is on the current data (and the history is secondary), make a clear distinction between the "current" table(s) and the "history" table(s).
• If you expect to have millions of rows in any table, I predict you will be back with performance problems. (But the schema and queries will have changed several times between now and then, so I cannot give you concrete advice today.)