A database is a representation of all of the data stored that helps facilitate your users functionalities. This means that your database architecture should be based off your features. Consider how your application is helping your users and what they will be doing on your application. If you are completely aware of the core features and user flow of the application you will be able to make informed decisions when designing and developing your database.
Say we were developing an E-commerce application where we are selling fruit. Here are some example user stories, can you think of more?
Users will need to login to order and purchase fruit
Users will need to be able to track their transactions
Users will need to be able to search for fruit within our database by name
Users will need to see the current stock of items
Users can send their orders to various addresses
Users who refresh the page, or continue an order on another machine, will not lose their cart
This user stories act as a guide when developing our database. Provided we begin to follow some of the conventions for developing a relational database. So before we begin to create our database schema we should delve into some relational database rules.
Here is an example of a poorly optimised database, we apply some rules and alter this so that it is possible to create a relational database with this information.
Each table cell should contain a single value and every record needs to be unique
As you can see from the table above there is an issue with the column fruit_brought, there are multiple items listed per cell, some repeating multiple times, we will need to alter our database to represent the data more efficiently for querying.
Example of the database with single values in each cell
What is a composite key?
A composite key in SQL can be defined as a combination of multiple columns, and these columns are used to identify all the rows that are involved uniquely.
Here is an example of a table that uses composite keys
Currently we are using composite keys to identify rows of data in the table above, full_name could be repeated, so we would actually reference the name and email to identify the user.
A primary key is:
A primary key is the column or columns that contain values that uniquely identify each row in a table.
Currently we are using Composite keys to reference data, however, we should develop tables that contain primary keys to help identify and reference rows within our table. Here are some rules regarding Primary keys.
Primary keys cannot be Null
Primary keys must be unique in value (per table)
Primary keys values should rarely be changed
Primary keys must be given a value when data is inserted
When using primary keys make sure that:
Primary Keys do not functionally depend on any subset of candidate key relation
Here is an example of the of the original table using primary keys to identify the information
Foreign Keys references the Primary Key of another table to reference that information set.
A foreign key needs to have a different name from its primary key.
It ensures rows in one table have corresponding rows in another.
Unlike the Primary key, they do not need to be unique on a table, often they aren't.
Foreign keys unlike Primary Keys can be null when you are setting the information within your database.
Below is a representation of our table constructed with Primary and Foreign keys.
Why do we need Primary and Foreign keys?
Primary and Foreign keys help us to define the relationships between our table, in this case, a 1-many relationship, a user can have multiple fruit. Without this relationship defined data integrity could not be handed by SQL.
Lets continue with our E-commerce fruit store example. We will need a few tables that define our relational database schema. As stated before the functionalities of the application will dictate the information that is stored within our database. So lets start with the first functionality,
Users will need to login.
If users need to login at some point in our application we are going to need to store their credentials within our database to validate that they are users within our application, this means we will need a table to store identifying information for our users.
Users who are logged in can order fruit
If logged in users can order fruit from our store we will need ensure that they are presented with an array of items that they can buy, this means we will need to create and populate a fruits table that represents all of the items we can sell.
Users will need to be able to search for fruit within our database by name
As stated above we are going to create a fruit table which will contain relevant information. The information that is retrieved from the database can be displayed and shown on the browser. This particular issue is more about implementation rather than database setup. There are two approaches to implementing this user story:
Click on a button to search for a particular fruit by name, this could call an API request that looks for a single fruit returning that data to be rendered on the screen.
Call an API that retrieves all of the fruit from the backend, and then using a state and filter function on the frontend, process the given fruit and only show one.
Users will need to see the current stock of items
As stated above, we are going to create a fruit table to store information regarding our fruits, if we want users to see the current stock then we just need to store the information in our Fruits table and display it when showcasing the fruits within our store.
Users will need to be able to track their transactions
Considering users will want to order fruit and track their previous orders we will need to develop two tables that are related.
An orders table to store the total and user_id
An ordered_fruit table, a junction table, to store all of the fruits within a particular order.
Here we have a one to many relationship, one order can have many ordered fruit.
Users can send their orders to various addresses
As user will be able to send their orders to various addresses we will need to store multiple user addresses, this means we will need to create a users_addresses table that contains each address that the user could send their fruit order to.
Users who refresh the page, or continue an order on another machine, will not lose their cart
This user story means that we will need to create a table that tracks fruit items and quantities of items in their cart, every time they add, edit or remove and item we will need to update this table. When the order is placed, you would remove all fruits in current_cart table and update the ordered_fruit, orders and fruit table.
This would be the resulting database:
Now that you have designed your database you can begin to develop the Sequelize migration and seed files that you can run to create the database on your server as well as populate it with data. When developing a database we split our tables into three main migration files.
Primary tables, tables that do not rely on any other data.
Secondary tables, a table that contains one foreign key, normally a one to many relationship.
Tertiary tables which represent our join tables, these tables will often contain two or more foreign keys.
Once you run your migration and seed files developers can begin work on the actual server with models, controllers and routers, sharing a common dataset.
Another commonly accepted rule of databases is that:
Data has no transitive functional dependencies
A transitive functional dependency is when changing non-key column (normal column), might cause any of the other non-key columns to change.
If a full name changes this might alter a salutation, and therefore we shouldn’t have this relationship within our schema. We will need to break the Salutation into another table that we can reference.
There are further rules, but lets not deal with those at this moment, if you want to read more, please look into this link.
