Recently I have created a relational DB in SQL and decided to share how I've done it. It's a snowflake design. Creating a relational DB is not difficult, but it's very difficult to change things after creating them. That's why we need to be very neat in the beginning, First I'd like to show you how it looks.
For representation, I've used EXCEL so that I can visualize the tables by using color codes. Every color represents one relation. While ID columns are primary keys ...ID columns are foreign keys at other tables. And below you can see how the tables look after entering data.
My first rule while I create a database is adding an ID column for every table and making it primary key and identity(1,1) so that it will increase automatically. The first thing we need for a DB table is a column that defines every row uniquely.
The second thing I try not to do is using dedicated words like 'PASSWORD' or 'USERNAME' for column names. I simply add underscore to the end of the name if it's a dedicated name for SQL.
Keeping the right data in the right table is a must. For example, I don't keep Addresstext column at Users table because there is a good chance a user can have multiple addresses like home address, business address, etc. If I keep it in the Users table then I would need to update it every time, which is not accepted in 2021's DB management systems.
Choosing the right column as key is also worth mentioning. For example, for the Address table, I've used UserID as a foreign key. I shouldn't use the Namesurname column because there might be many people with the same name and surname, the username might change too. The only thing that is not going to change is ID column.
I can't emphasize enough how important choosing the right data type for the columns. By using the right data type we can save enormous space in our database. For example, for the CountryID column in Address table I've used tinyint because I know there are less than 255 countries in the world. And it's not about only the storage performance. The smaller the number of bytes query processes, the faster the query works.
You might notice that Amount, UnitPrice, and LineTotal columns are repeated both in Orderdetails and Invoicedetails tables. The reason for that, sometimes orders can be delivered piece by piece if the provider can't provide all the orders at once. If that's the case, there's a good chance that the order will be delivered to different addresses. That's why I repeat adressId at Invoce table. A product can be sold at different prices is the reason why UnitPrice column is repeated.
Today I'd like to finish my post with an error message:
When I was creating the database, I got this message. I choose tinyint as datatype for the ID column at the Countries table. But at the Address table, where that ID column was supposed to be the foreign key, I choose smallint as datatype. When I tried to create primary key foreign key relationship, I got this error message. So it's essential to choose the same data type for the primary key and foreign key columns.
These were the things that I needed to be careful while I was creating the database. You can guess that there are more issues we should be aware of during the process. No post would be enough to share them all. It also depends on the content of the database. This is what came out of my experience. I hope it can be useful for you.
Comments