Finalization of Database Design Specifications - MySQL

Now that we have basic knowledge of different MySQL data types and rules in naming the database and tables, we can finalize the database specifications of your web application. It is highly recommended that you write down the specifications on a piece of paper or in a Microsoft Word file regarding your planned structure of the database before submitting a query to MySQL.

Below are the recommended specifications:

First field:

Data to be gathered: Customer name

Recommended Field name: customername

MySQL data type: VARCHAR

Maximum length of customer name allowed: 64 (this can be extended, for illustration purposes only)

Second field:

Data to be gathered: Price

Recommended Field name: price

MySQL data type: DECIMAL

Maximum number of allowable digits before decimal point: 4

Maximum number of decimal places: 3

Final decimal length specification: DECIMAL (4+3, 3) OR

DECIMAL (7, 3)

No negative values allowed

Third field:

Data to be gathered: Date purchased

Recommended Field name: datepurchased

MySQL data type: DATE

Maximum Length: not applicable

Fourth field:

Data to be gathered: Product Serial Key

Recommended Field name: productkey

MySQL data type: SMALLINT

Maximum character length of this variable: 5

Other attributes: Zero-fill if less than 5 digits; no negative values allowed

Fifth field:

Data to be gathered: Complaint details

Recommended Field name: complaintdetails

MySQL data type: TEXT

Maximum Character length: Depends on user input

Sixth field:

Data to be gathered: Receiving date of complaint

Recommended Field name: receivingdate

MySQL data type: TIMESTAMP

Other attributes: Set "default" value to "Current_Timestamp" so that it will return the actual date of every MySQL data insertion.