I design some tables that allow data to be assigned to an object to track the progress of that object, as well as functions for storing emails sent in connection with those contracts and their data. Suppose the object is a contract (a contractual task to be performed).
That means we have a contract table
Now we can have date fields for this contract. For example, a received date, a start date, a review date, an estimated end date, an end date, and so on. Currently, most contracts contain the same columns, but we do not know in the future if there are any more columns to be added or less will be used Change requirements frequently.
For this reason, I have decided to separate the data 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 is the date. And finally, an AuditTrail type table named ContractDateHistory that logs the changes to a date.
In this way, we can also selectively select which data belongs to a contract. One contract can have 4 dates and another 6 dates.
With these contracts, emails can be sent to persons who inform about the expiration of this data or the change of a date. Because multiple contracts can be included in an email, I have a ContractsForEmail table that contains the ID of the email for each contract that is included in an email.
For each e-mail sent to a user, a line is entered. NeedsResolution is present when the user needs to take action with the email (depending on the email type), and resolved is present when the user has taken action. A program verifies that the user has taken action after a set period of time. Otherwise, the program will send a reminder e-mail (which may also appear in this table).
The template can contain a list of users to whom the emails derived from the template are sent (for each user assigned to the mailing list of a template, an email is sent, but additional users can be sent, An e-mail is sent here.
I have many questions about whether my design is okay, but I would like to know what I do with contracts and contract appointments. Is it okay?
Should I simply add the "Date" columns to the "Contracts" table instead of separating "Contract" and "Date"?
If they get used to it, then it's good, and if they do not do it for a particular contract, it's just considered ZERO.
This can cause columns in the contract table to be added and never used again (almost always NULL). How so:
AddedDateInFuture is intended to illustrate that a column may be added in the future and no longer exists.
- What are the trade-offs between the two options?
- Is something missing in the email tables? Would this type of design work?
Currently, the system would serve fewer than 200 users, but I want it to be a little future-proof when used by many more users.
As a final note, some database tables, such as the usual "user" table, have been omitted.