Database Design: How to Decide What Column Types to Use in MySQL Databases

6834701631729689172
This post is assuming you have already determined your database and tables structure in your MySQL design. The next step would be to specify what types of data, length of the column data, if it can be null, and possibly default values.

First, what type of data will this column contain? The three primitive data structures in MySQL are text, number, and date/time.

Within the text data type, the subtypes are the following:

  • CHAR(LENGTH) - Fixed-length field from 0 to 255 characters long.
  • VARCHAR(LENGTH) - Variable-length field from 0 to 65,535 characters long. 
  • TINYTEXT - A string with maximum length of 255 characters. 
  • TEXT - A string with maximum length of 65,535 characters long.
  • MEDIUMTEXT - A string with maximum length of 16,777,215 characters long.
  • LONGTEXT  - A string with maximum length of 4,294,967,295 characters long. 


Within the number data type, the subtypes are the following:

  • TINYINT(LENGTH) - A number value that goes from -128 to 127 or, if unsigned, 0 to 255. 
  • SMALLINT(LENGTH) - A number value that goes from -32,768 to 32,767 or, if unsigned, 0 to 65,535.
  • MEDIUMINT(LENGTH) - A number value that goes from -8,388,608 to 8,388,607 or, if unsigned, 0 to 16,777,215.
  • INT(LENGTH) - A number value that goes from -2,147,483,648 to 2,147,483,647 or, if unsigned, 0 to 4,294,967,295.
  • BIGINT(LENGTH) - A number value that goes from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,808 or, if unsigned, 0 to 18,446,744,073,709,551,615.
  • FLOAT(LENGTH, DECIMALS) - A relatively small floating decimal point number. 
  • DOUBLE(LENGTH, DECIMALS) - A large floating decimal point number. 
  • DECIMAL(LENGTH, DECIMALS) - A large floating decimal point number stored as a string. 


Within the date/time data type, the subtypes are the following:

  • DATE - Date in format YYYY-MM-DD.
  • DATETIME - Date and time in format YYYY-MM-DD HH:MM:SS. 
  • TIMESTAMP - Date and time in format YYYYMMDDHHMMSS. 
  • TIME - Time in format HH:MM:SS. 

The length parameter is optional and specifies the maximum length the column can hold. It is best practice to specify a length for all applicable columns. Obviously, the length should be what might be the longest entry made. For example, if the column is to store last names, a length of 40 should suffice. If the entry is longer than the specified length, the data is truncated to the length.

Every column can be defined as NOT NULL as well. This means the column must have a value for every entry. For example, the first line of an address should be set to NOT NULL while the second line of an address for apartment or suite number should be left optional. A sample code would look like this:

CREATE TABLE addressbook (
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(40) NOT NULL,
address_one VARCHAR(60) NOT NULL,
address_two VARCHAR(60)
);

In the example above, the first and last names are variable-length strings with maximum length of 40 characters and are both required. The address_one column corresponds to the first line of the address is is required while the address_two column corresponds to the optional second line of the address.

Number data type columns can also be specified to be UNSIGNED. Doing so will force the column to range from 0 to the maximum positive number the subtype can hold. The AUTO_INCREMENT property can be specified as well to make the column automatically add one onto the previous value upon a new entry. This is good for setting the table's index number.

There is also another way to specify columns using the ENUM and SET types:

  • ENUM('OPTIONONE', 'OPTIONTWO', ...) DEFAULT 'OPTIONTWO' - This allows you to choose one option among a predetermined set of options. Only ONE of the options can be chosen in one entry. Setting a default is optional. If the default is not set and no option is selected upon row entry, the first option will be selected (in this case, OPTIONONE). 
  • SET('OPTIONONE', 'OPTIONTWO', ...) DEFAULT 'OPTIONTWO' - This allows you to choose up to 64 options among a predetermined set of options. Multiple options can be chosen in one entry. Setting a default is optional. If the default is not set and no option is selected upon row entry, the first option will be selected (in this case, OPTIONONE). 

I hope this wasn't too confusing to follow and understand. I am still learning and hope I was of some help. Good luck with your database design! Shoot me with any questions and I will do my best to answer.

Comments

Post a Comment

Popular posts from this blog

Angular (2+): Core vs Shared Modules

Angular: Core vs Shared Modules If you're new to Angular, you might get confused between these two commonly used modules. So what do you put in either components?  What goes in CoreModule? Your CoreModule contains code that will be used to instantiate your app and load some core functionality.  The clearest and most important use of the CoreModule is the place to put your global/HTTP services. The idea is to make sure only one instance of those services will be created across the entire app. The CoreModule, by convention, is only included in your entire app once in AppModule (only in the "import" property of the "@NgModule()" decorator inside your main app.module.ts, not in any other module's "import") and this will ensure services inside it will be only created once in the entire app. This is especially important if you intend to lazy-load your feature modules. Since lazy-loaded modules are loaded on demand (eg when you access the rou...
Read more

Home Improvement: How to Make Your Toilet Flush Stronger.. Without Opening Toilet Tank

Disclaimer: This only works with traditional toilets with the toilet tanks that you find in residential buildings and if nothing is broken in your toilet!  TLDR: Hold down the flush lever a little longer than usual. Toilet Basics: If you're new to toilet terminology, there's a distinction between toilet tank, which is the part that houses the lever, water, and all these parts you see in diagram, and toilet bowl, which is the part you excrement goes in and out of. Here's a diagram of what my toilet tank parts looked like: Toilet tank parts diagram. Courtesy of https://removeandreplace.com/2013/09/09/fix-toilet-constantly-running-diy-toilet-repair-step-step/. So the problem: the past couple of days, I noticed one of the toilets in my home sounded like it was running a water leak constantly. After some Googling and watching toilet repair videos on Youtube, I was able to diagnose the issue - a faulty flapper (the red part in the diagram) - by lifting the toilet ...
Read more

Food: How Carrot-Coconut Sunny-Side-Up Egg is Made

I was watching Netflix's "The Mind of a Chef" and came across this interesting creation called Carrot-Coconut Sunny-Side-Up from Wylie Dufresne (Season 1 Episode 9 at around 13:20). He was able to create something with the consistency and look of an egg without using any eggs. What fascinated me was the combination of ingredients and cooking methods. The egg white is made with: Coconut milk Cardamom Sugar Locus bean gum Guar gum Xanthan gum All that is mixed into a pot and simmered and mixed. After some undisclosed time, it is simply scooped out and put on the serving plate where it will immediately cool down and congeal to the "egg white."  The egg yolk is made with: Carrot juice Smoked maple syrup Salt Glucose (so it doesn't freeze solid) Xanthan gum (to give egg yolk consistency) The egg yolk covering/gel is made with: Water Kappa carrageenan gum Locus bean gum The egg yolk ingredients are mixed, put into a mold that re...
Read more