E-Commerce Data Base With MSSQL

Tuğçenur Erdoğan
7 min readDec 15, 2020

--

Hi Everybody!

I will design an e-commerce database.

Firstly, I go into the Microsoft SQL Server Management Studio 18.

Figure 1
Figure 2

I am creating a database named e-commerce by following the steps in Figures 1 and 2.

Figure 3

When I clicked “New Database Diagram” as Figure 3, the screen you see below is coming as Figure 4.

Figure 4

Click on Yes !

Figure 5

When you click “Yes”, the screen in Figure 5 is coming. You should click on “New Table”. Because we want to create new tables.

Figure 6

The first thing on an e-commerce site is to collect users’ data. It can be called the first step, but we can start from any part of data collection. For example: products, addresses, cargo information, supplier information, etc.

First of all, I chose to create a users table.

Figure 7

When I write the name of the table I want to create and click on “OK”, the empty table in Figure 7 appears.

Figure 8

I fill this empty table as in Figure 8. In SQL, we can select the parts that should not be null when entering the data with “Allow Nulls”. And we determine the Data Type according to the type of the variables.

For example, UserID becomes integer since it will consist of sequential numbers, nvarchar will hold multi-character data, the numbers in parentheses allow us to determine how many characters of data to hold. The key symbol to the right of the UserID has caught your attention.

Figure 9

That key was created as you see in Figure 9.

Well, What is this primary key?

When creating tables in Relational Database Management System, a column is created as the identification number of each record of the table. In this column, a unique value is given for each record of the table. This can be thought of as the National Identification Number. In other words, it will be sufficient to give the unique ID of that record to find the record we want while querying.

What other data is kept in an e-commerce database?

Products Information, Address Information, Cargo Information, Delivery Information, Suppliers etc. Can be diversified.

Let’s start creating the other tables. I’m following the steps I followed while creating the Users table. And I get the tables you see in Figure 10a and 10b.

Figure 10a
Figure 10b

When you look at Figure 10a-b, the connections between tables have caught your attention. These represents Foreign Keys.

Well, what is Foreign Key and how do we create it?

Foreign Key is used to connect to two tables in relational databases. Foreign Key refers to the combination of columns whose values ​​match the Primary Key of a different table.

In SQL databases, the primary key in one table and the foreign key in the other table are used to establish the relationship between two tables. The main purpose of the FOREIGN KEY constraint is to ensure that primary key data is repeated over and over in other tables and data consistency.

If it comes to the part how you did it,

I am determining the two tables that I want to connect. I press and hold one of the two, then I move from it to the other table and stop press. The screen in Figure 11 seems.

Figure 11

For example, I want to use SupplierID data to Supplier Table and Products Table. SupplierID is Primary key in the Supplier Table and it is Foreign key in the Products Table.

Click on “OK”.

Figure 12

The screen in Figure 12 seems. Again click on “OK”.

By the way, let’s not neglect to do what is shown in Figure 13–14–15 on all primary keys.

Figure 13

When we right click on the any primary key any table, you can see option properties. When you click this option, can see the screen in Figure 14. If you click twice “(Is Identity) “ , “No” turns “Yes” as Figure 14 and 15.

Figure 14
Figure 15

When I want to save the tables I created, “Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enable the option Prevent saving changes that require the table to be re-created” i will come across this error. The reason we encounter this error is the purpose of “mssql server 2008 r2” to protect us. It is to prevent the variable type of the column in the table from changing without permission. Afterwards, we also encounter an error like the following. (“User canceled registration dialog MS Visual Database Tools”)

To avoid these errors, you should follow the steps below!

We need to remove the tick next to “Prevent saving changes that require table re-creation” option. This way we can get rid of the mistakes.

At the end of the registration process, my tables will look like the following.

Now let’s talk about the method of creating a database using codes and commands:

CREATE TABLE Addresses

(

AddressID int

AddressType nvarchar(50)

Billing_Address nvarchar(50)

Delivery_Address nvarchar(50)

UserID int

PRIMARY KEY (AddressID)

)

If I need to talk about what I’ve done here, I’m creating a table. This is how I create the tables that contain the data I want to keep. I’m creating the tables with the “CREATE” command. I don’t forget to add the column names. With the “PRIMARY KEY” command, I specify the column where the primary key will be made. You have to write the above code snippet for each table.

Then, I added foreign keys. You can do this while creating a table, but I chose to add it later.

ALTER TABLE Addresses

ADD FOREIGN KEY (UserID) REFERENCES Users(UserID)

The “ALTER” command allows us to add. We specify the name of the table we want to add with “ALTER TABLE”, then we say that we want to assign a foreign key to this table with the “ADD FOREIGN KEY” command. Again you have to repeat this for all foreign key assignments. Tables and columns will be formed after all this. You can check them as follows with the “SELECT” command:

SELECT * FROM Addresses

Result of this command :

This table will fill as you fill the columns with data.

SELECT Billing_Address FROM Addresses

This command will be run, result is :

So what should we do if we want to fill these tables with data?

What commands will we use?

“INSERT INTO” is the command used to add data to the created tables.

INSERT INTO Users (UserID,Namee,Surname,Email,Telephone,TC_Nu,Gender,Passwordd) VALUES (1,’Tuğçe’,’Erdoğan’,’tugce12@gmail.com’,’05050505050',’12345678910',’Female’,’0000')

or

INSERT INTO Users VALUES (1,'Tugce’,’Erdoğan’,’tugce12@gmail.com’,’05050505050',’12345678910',’Female’,’0000')

When you execute the above line, we encounter the following screens.

SELECT * FROM Users

When I run the above command, I can observe the line I added.

That’s all I have to say for now.

Thanks for reading this first article in which I tried to explain how to create an e-commerce database in MSSQL in the simplest and simplest way.

Have a nice day.

--

--