Written by: Michael Boddie;
The following is the entity relationship diagram that I designed using MySQL Workbench. I have included all the primary and foreign keys as well as defining the datatypes for the variables.
The process I went through is defining the relationships between the various tables that I designed. I had to identify the type of relationship that would exist between various entities. For instance, the relationship of one-to-many where one value in one table would appear as many values in the related table. For instance, a user may exhibit many orders and can result too many payments. This is a one to many relationship which ensures that the primary key of the main tables appears as the foreign key in another table which they are related. There was also a one to one relationship because one user can have only one payment card.
Selecting the datatype was an important process that would ensure the most appropriate characteristic of the data variable is incorporated. I ensured that I included all the primary key have the INT datatype so that I can reuse them as foreign keys without datatype mismatching. The timestamp variables would involve DATETIME variable because they would keep a record of the date and time of the exact transaction. Also, I included the DOUBLE datatype for all the currency variables because they would precisely define the exact amount of money which is precision sensitive.