The diagram created is called an Entity Relationship (ER) diagram and it shows how different objects are related to one another within a system. Notice the boldfaced database name at the outer border showing that it is the main container, and within this container is a single rectangular table with its boldfaced name at the top and its columns (or attributes) below it? When designing a database, it is always a good idea to have a visual representation 💡.

Data Types

With the naming done, it is time to focus on the data types for the columns in the Coffee table, and for now on I will be referring to columns as attributes.

A data type is what defines the type of data an attribute has, and the types of data are integers, characters (i.e. strings in the programming world), dates/times, and a few others. There are a wealth of intricacies within data types, and if you look at online educational information and documentations such as SQL Server Tutorial, Microsoft, and the Department of Computer Science at University of Toronto, you would notice expansive descriptions. This article will not cover every detail for data types, but I will talk about some that will be used for the attributes in the Coffee table: VARCHAR, INT, and DATETIME.

VARCHAR

If you take a look at online resources, you will see that VARCHAR and CHAR is to store characters which may appear that they do the same functionality. In reality, they do not. VARCHAR stores data in variable length whereas CHAR stores it in a fixed length. What does that mean? Here is an example: let us say you want the type attribute to have a maximum length of 10 characters, and you want to use the CHAR data type. To define this attribute would be type CHAR(10) where CHAR(10) is saying that you want this attribute to have 10 characters in a fixed length. You add "Regular" into the type attribute which has 7 character counts. You want to see the length that you just inserted with the command LENGTH(type), and it turns out that the length is 10! This is because if the string is less than the fixed length in the CHAR data type, then it pads that string with extra spaces to match its fixed length. If you display the string you just inserted to the type attribute, it might appear as "Regular " instead of "Regular".

To overcome this caveat, you can use VARCHAR which stores data in variable length. Let us say that you want to use VARCHAR instead of CHAR for the type attribute. You can define it the same as before with type VARCHAR(10) and add data of "Regular". Now, when you check its length, it shows a 7 character count with no extra paddings to "Regular". NOICE 🥳!

With this in mind, let us define the data types for the attributes: sku with sku VARCHAR(12) since SKU are typically between 8-12 characters long; type with type VARCHAR(15) since you know you would have 19 different coffee types with the longest name being "Double Espresso" of 15 character count; and name with name VARCHAR(50) since you are not sure how creative and long the names of your coffee drinks will be, but you are sure it should not be anymore than 50 characters.

NOTE ✍️: The size attribute will be dealt with further down below.

DATETIME

For the attributes created_at and updated_at, you want to store the data as a date which the DATE data type could do by storing it in the format as "YYYY-MM-DD". If you add "Coffea Americano" to the database, it would display it as "2022-08-22". You then think about it and it turns out that maybe having only the date is too vague since you also want to know the time when a product was added to the Coffee table. This is where the TIME data type would come in handy by storing the data in a 24-hour format of "HH:MM:SS". Now, "Coffea Americano" would display it as "19:27:03". Wait! What about the date?! This is where the DATETIME data type would come in handy by storing the data in both the date and time formatted as "YYYY-MM-DD HH:MM:SS" (there is a space between "DD" and "HH" 😮). Now, your favorite coffee "Coffea Americano" is being displayed as "2022-08-22 19:27:03".

You enter your new commands for created_at and updated_at: created_at DATETIME and updated_at DATETIME.

Wait just a moment!! Does the DATETIME data type store it as a UTC (coordinated universal time), or does it store it on your current date and time without changing it to a different time format for another country? Do not worry! This will be dealt with once we start working with Django 🥳.

PRIMARY KEY, NOT NULL and INT

When it comes to databases, all tables need to have a PRIMARY KEY which identifies each record in a table, and it needs to be unique. It also should be mainly static without changing its value, and this is because you do not want to be inconsistent. Imagine if you were inconsistent, then this might create conflicts and errors on your Frontend app when it is relying on that inconsistent PRIMARY KEY. Originally, it was pointing to X but you updated the value to be Y. Now, it is inconsistent leading to confusion or errors. For your "Genus Coffea" app, you could use the sku attribute as its PRIMARY KEY, but this would most likely be inconsistent since you might update the SKU to organize your products for your store differently. So, what attribute could you use or create as the PRIMARY KEY?

For databases, you would add an extra attribute to tables, and by convention it would be called id. Now, id is your PRIMARY KEY, but it needs to be unique. What is the best way to store unique data? Integers. And the data type for integers would be INT.

Also, remember that the PRIMARY KEY is what identifies each record in a table. Since this is important, this means that the id attribute cannot be empty. If you insert "Coffea Americano" but forgot to insert an integer to its' id attribute, you have created errors on your Frontend app since your id is now empty (this would be null in the world of JavaScript). Not only that, how do you identify "Coffea Americano" if you let the id attribute to be empty? It cannot be the sku since you might change that value some other time. To overcome this and leave constraints, there is NOT NULL where you are saying that the attribute cannot be empty. If you forgot to add an integer value to "Coffea Americano" for the id attribute and submit it, it would not insert "Coffea Americano" to the table and give you an error message telling you that the id attribute cannot be null. Luckily, most relational database management systems (RDBMS) like MySQL or PostgreSQL would automatically generate integers for you without needing to insert it. In Django, the id attribute comes by default to every table so that you do not need to define it. Noice 🥳!

PHEW 😮‍💨! That is a lot for the id attribute. Nonetheless, it is important. You begin to type your new command: id INT NOT NULL PRIMARY KEY.

With your new found knowledge of NOT NULL, you look at some of your other attributes and wonder if they should have constraints as well: sku, type, and name. This is something you would have to think about on what is important for your needs (such as business needs). type and name seems pretty important: You need to know the name of the coffee, and you need to know what type it is. Is it an espresso or a regular coffee? sku might be less important since you might not have the SKU prepared beforehand which is okie. Therefore, type and name have constraints of NOT NULL while sku is NULL.

Your updated commands for type, name, and sku are type VARCHAR(15) NOT NULL, name VARCHAR(50) NOT NULL, and sku VARCHAR(12) NULL, respectfully.

NOTE ✍️: As a tiny refresher, let us say that you gave a value to the name attribute but not the type attribute in the Coffee table. Once you submit it, it will not go through because the type attribute cannot be null.

MO' NOTES ✍️✍️: As another tiny refresher, you gave values to both the name and type attributes but not the sku attribute. You submit it and it successfully goes through without errors because the sku can be null.

What About the size Attribute?!?!

It may not seem like a big deal since you are storing different sizes. Unfortunately, it is. Remember that spreadsheet from Figure 1 you created for Genius Coffea? "column D" in that spreadsheet can have three different sizes: "Small", "Medium" or "Large", or it could be a combination with these three. For your "Double Coffea" coffee, it has only one size which is "Small". On the other hand, "Coffea" has three different sizes of "Small", "Medium", and "Large". When creating the size attribute for the Coffee table in the Genus Coffea database, storing one value is fine whereas three values might not be. Imagine this: you are trying to update the "Coffea" coffee to have only two sizes, but you might have made some mistakes for the values such as forgetting to put a comma, or accidently removing all sizes leading to an error because it cannot be null. This is where the process of normalization would help by organizing data, reducing redundancy, and avoiding unintended side effects and errors.

To Be Continued...

Unfortunately, since this article has a lot of information for you and the reading time is more than it should be, the normalization process will have to occur until next time. Luckily, that article on normalization is finished 🎊! For your convenience, here is the article w/ link: Starting with Django Part 3: Normalizing the Schema Design 👈.

P.S. I know that this might be a lot to take in and you might feel overwhelmed, but just know that being a programmer (or any field) takes time. You might not understand it now, but in a few months or years, you will look back and be glad that you put yourself through the struggle so that you can become a beautiful software engineer 😘.

Happy coding ☕!