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


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.

Contract and Date table seperate with a history of dates table.

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.

Contracts with emailing tables

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

Contract table with dates inside

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.

Are error variables an anti-pattern or good design?

In order to handle several possible errors that shouldn’t halt execution, I have an error variable that clients can check and use to throw exceptions. Is this an Anti-Pattern? Is there a better way to handle this? For an example of this in action you can see PHP’s mysqli API. Assume that visibility problems (accessors, public and private scope, is the variable in a class or global?) are handled correctly.

What would be a best design for a ‘cloud relay’ application?

I’d like to build a client/server application that doesn’t require the client to open a port in its firewall for incoming connection requests. In other words, the clients will be able to communicate with each other over the outgoing connections that they initiate with the server.

This will be very similar to, for example, any of several popular desktop remote control offerings available today. VNC Cloud comes immediately to mind.

My initial thought is to use SignalR at the server to accept connections from—and keep a channel open with—each client. When Client A wants to send something to Client B, it will contact the server with the appropriate routing command, which the server, in turn, will send to Client B.

(Note that this idea is based on my limited understanding of SignalR—I haven’t yet had an opportunity to work with it yet. This would be my first.)

How do these folks build these applications, in general? Is it something like what I’ve described here?

If my SignalR idea isn’t the best way, what is?

Experimental design, ” how chatbots can impact on a user’s experience”, is better to use Within subjects or between subjects?

I have to do an experimental design “about how chatbots can impact on a user’s experience when using public transport websites to book journeys”. I am not really sure what type of experimental design to choose, would go for Within subjects or between subjects ?



Design Contest – FB Ad and Poster Board – $50 for each – 24 hr Contest

Hi Contestants! We would like to submit a design contest for a Facebook ad, as well as a Poster Board/Sandwich board design. Hopefully it can be all in one, but we know that's a far reach. The winner will get $ 50 for each of the separate contests or $ 75 if we think its good enough for both. We may even pick a couple designs if we like a few of them.

Sandwich Board Sign dimensions are 36" x 24"

We would like to include the following information/pic/logos on the design.

New Hours…

Design Contest – FB Ad and Poster Board – $ 50 for each – 24 hr Contest

How to utilise empty space in card list design?

My website’s main page displays summaries of users’ profiles. Each profile summary is encapsulated in a “card”.

Take a look at an example here (using dummy data).

enter image description here

For reference, the card width is about the width of the main content <div> on StackExchange websites.

As you can see, there is a lot of empty space between the main information area on the left and the contact button on the right.

I wish to avoid multiple cards per “row” as I think that is confusing; the benefits of the current design are that they are easy for our brain to follow and read information.