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 a 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 less 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 of 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.
I have many questions about if my design is okay but in specific I’d like to know, is 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 many more use it.
As a last note some database tables such as the usual “User” table has been left out.