Published on

Designing an Operational Database for Circle K: A Comprehensive Approach

4414 words23 min read
Authors
  • avatar
    Name
    Kevin Nguyen
    Twitter

clipboard.png

Overview:

Circle K is a global convenience store and gas station chain with over 10,000 locations in North America, Europe, Asia, and the Middle East. The company offers a wide range of products and services that cater to the convenience of customers, including fuel, snacks, beverages, cigarettes, lottery tickets, money orders, prepaid cards, car washes, and ATM machines. With its commitment to sustainability and support for local communities, Circle K has become a well-known brand that provides accessible and convenient services to customers worldwide.

Due to its popularity and convenience, Circle handles hundred or even thousand of transactions per day. Circle K must find a way to store all those transactions to be able to manage the stores more efficiently as well as grow more sustainably. Specifically, once Circle K stores data effectively, it can utilize and analyze those data with the purpose of tracking revenue, inventory, or other key performance metrics. Therefore, it is necessary for Circle K to have a good operational database. This is the reason why this project will focus on designing an operational database for managing transactions of Circle K.

In general, this project includes 4 main parts:

  1. Case description
  2. Design Entity Relationship diagram (ERD)
  3. Database normalization
  4. Create trigger to catch the event of insertion

Case description:

Circle K offers a wide range of products with various designs and origins, including bottled drinks, beer, wine, or ready-to-drink beverages such as coffee, lemon tea…; and many types of cakes, snacks, and even fast food are fully served here.

Depending on the store size and location, the number of POS machines may differ, usually ranging from 2 to 3 machines. Each POS machine has its own unique identification code.

To create the most convenient conditions for shopping, customers who come to buy products at Circle K will have many choices of payment methods, such as: cash, ATM, Momo etc

For each transaction, customers can buy multiple products at the same time. When customers make payments at the counter, after the staff scans the price of each product, they will ask customers if they are a member of Circle K to accumulate points. For customers who are not yet members, they will not earn points, and these transactions will not have customer information. For Circle K members, to earn points, they will be asked by staff to open the CK Club app on their phone and scan the “My ID” code. After earning points and making payments, the CK Club app will automatically save the transaction just made in the app and update the total amount spent from the first purchase at Circle K until the current time of the customer. Specifically, as a Circle K member, based on the total amount spent, customers are divided into different levels in the total of 4 levels currently in the store’s system, including Silver, Gold, Platinum, Diamond. Silver level is the lowest level, when a member signs up for the first time, the customer is in the default Silver status. To reach a higher level, customers need to spend a total amount of money according to each level’s regulations. For example, t o go from Silver to Gold level, customers need to spend a total of 1,000,000 VND, to reach Platinum, customers need to spend a total of 5,000,000 VND (including 1,000,000 VND to go from Silver to Gold). The system records the information of members such as name, date of birth, phone number in each of their transactions. In addition, once customers reach the highest level, Diamond, they will still be able to earn points, but will no longer advance to a higher level.

Circle K regularly provides promotional programs to customers through discounts on store items. Each program has a start and end date, and for simplicity, the hypothetical project only includes two main types of promotions: percentage discounts and buy one, get one free. Sometimes a product can be eligible for both types of promotions at the same time. Therefore, for each product in a transaction that is eligible for a promotional discount, the database system will record which promotion was applied and how much percentage was discounted.

Entity relationship diagram:

Circle K Entity relationship diagram includes 13 entity types. There are two weak entity types, which means the primary key of each entity type is a combination of the PK of the parent table and its columns:

  1. Transaction detail: PK is the combination of transaction_id in Transaction table and ordinal_number
  2. Point balance: PK is the combination of customer_id in Customer table and the update_date

In addition, there is also a Promotion entity type, which is used to record all of the promotions of Circle K. Moreover, I created a Title history entity type to record the historical positions of each employee in case of needing more information relating to the employee.

In terms of cardinality, one thing I would like to mention is a transaction can record the information of a customer if he/she is a member of Circle K. In case, the customer has not registered for membership yet, the transaction can not record the information of the customer. All of that lead to the result that the Transaction entity has an optional relationship with the Customer entity.

clipboard.png

Database Normalization

After converting business requirement into ERD, our work is not yet finished. We need to analyze the tables for redundancies that can make the tables difficult to use.

But why redundancies can make a table difficult to use ?

Well, a good database design ensures that users can change the contents of a database without unexpected side effects. It means that if a database can not reduce or eliminate the redundancies of data, it will lead to the modification anomalies issue, which are unexpected side effects that occur when changing the contents of a table with excessive redundancies.

You can check those keywords on google for more understanding because I can not explain all of that in this project. It’s not my scope today dude!

Before normalize table, we need to convert the ERD into a relational database first, then identify all of the functional dependencies of each table. Finally, using the Third Normal Form rules to normalize our database.

Note: if you do not know what are functional dependencies, just google it. In general, by identifying the functional dependencies, we can determine whether a table violates the Third Normal Form or not.

1. ERD conversion

After the conversion, we can see clearly the PK and FK of each table.

clipboard.png

2. Identify Functional dependencies (FD)

1. Terminal:

  • Functional dependency: terminal_id -> terminal_name

  • Reasoning: The terminal_id uniquely determines the terminal_name.

2. Territory:

  • Functional dependency: zip_code -> city, description
  • Reasoning: The zip_code uniquely determines the city and description.

3. Store:

  • Functional dependency: store_id -> address, zip_code, total_width, total_length, total_area
  • Reasoning: The store_id uniquely determines the address, zip_code, total_width, total_length, and total_area.

4. Payment:

  • Functional dependency: payment_id -> payment_name, category, description
  • Reasoning: The payment_id uniquely determines the payment_name, category, and description.

5. Employee:

  • Functional dependency: employee_id -> working_date, full_name, gender, birthday, phone, email, certify_no, full_current_address, full_permanent_address, zip_code
  • Reasoning: The employee_id uniquely determines all the other attributes in the Employee table.

6. Title:

  • Functional dependency: (employee_id, version_no) -> begin_date, end_date, title
  • Reasoning: The combination of employee_id and version_no uniquely determines the begin_date, end_date, and title.

7. Supplier:

  • Functional dependency: supplier_id -> supplier_name, supplier_origin
  • Reasoning: The supplier_id uniquely determines the supplier_name and supplier_origin.

8. SubCategory:

  • Functional dependency: sub_category_id -> sub_category_name, category
  • Reasoning: The sub_category_id uniquely determines the sub_category_name and category.

9. Product:

  • Functional dependency: product_id -> product_name, unit_price, description, sub_category_id, supplier_id
  • Reasoning: The product_id uniquely determines the product_name, unit_price, description, sub_category_id, and supplier_id.

10. Promotion:

  • Functional dependency: promotion_id -> promotion_name, start_date, end_date, promotion_type, discount
  • Reasoning: The promotion_id uniquely determines the promotion_name, start_date, end_date, promotion_type, and discount.

11. Membership:

  • Functional dependency: rank_id -> rank_name, total_spending
  • Reasoning: The rank_id uniquely determines the rank_name and total_spending.

12. Customer:

  • Functional dependency: customer_id -> created_date, full_name, gender, birthday, phone, email, certify_no, zip_code, current_rank
  • Reasoning: The customer_id uniquely determines all the other attributes in the Customer table.

13. Point balance:

  • Functional dependency: (customer_id, update_date) -> total_spending, next_rank
  • Reasoning: The combination of customer_id and update_date uniquely determines the total_spending and next_rank.

14. Transaction:

  • Functional dependency: transaction_id -> transaction_date, store_id, employee_id, terminal_id, customer_id, total_quantity, total_amount, VAT, net_amount, payment_id, cash_received, change_due
  • Reasoning: The transaction_id uniquely determines all the other attributes in the Transaction table.

15. Transaction detail:

  • Functional dependency: (transaction_id, ordinal_number) -> product_id, quantity, original_total_amount, promotion_1, promotion_2, total_amount
  • Reasoning: The combination of transaction_id and ordinal_number uniquely determines the product_id, quantity, original_total_amount, promotion_1, promotion_2, and total_amount.

3. Third Normal Form

In short, a table is in Third Normal Form if it meet those 2 rules

  • Each non-key column depends on all candidate keys, not on a subset of any candidate key.
  • Each non-key column depends only on candidate keys, not on other non-key columns.

From those two rules and based on the functional dependencies we just identify above, we can determine which table violates the Third Normal Form.

There are four tables that violate the Third Normal Form: store, employee, customer, and product.

  1. store, employee, customer: city depends on zip_code, although zip_code is a determinant but it and city are non key columns
  2. product: category depends on sub_category, although sub_categoryis a determinant but it and category are non key columns

To convert those tables into tables that follow TNF, we will split those tables into smaller tables and the PK of smaller tables are those determinants but not candidate keys in the original tables

clipboard.png

Create trigger to catch the event of inserting transaction

I created a trigger that manages the point balance of customers based on their transaction history. The trigger fires after each insert into the “Transaction” table, and performs the following actions:

  1. Declares several variables to hold information about the customer, their current rank, the next rank they could achieve, and the total amount spent.
  2. Opens a cursor to fetch the transaction date and customer ID from the inserted rows.
  3. Loops through each inserted row and performs the following actions.
  4. Retrieves the customer’s current rank from the “Customer” table.
  5. Retrieves the name of the current rank from the “Membership” table based on the current rank ID.
  6. Calculates the total amount spent by the customer from the “Transaction” table.
  7. If the customer is not already at the highest rank. which is Diamond level, it sets the ‘next rank’ column to be the current rank + 1, retrieves the minimum amount needed to reach the next rank from the “Membership” table, and checks if the customer’s total spending is greater than or equal to that amount.
  8. If the customer has reached the next rank, updates their current rank in the “Customer” table, sets the next rank after the update, and inserts a new row into the “Point balance” table with the customer’s ID, the transaction date, the total spending, and the next rank after the update.
  9. If the customer has not reached the next rank, inserts a new row into the “Point balance” table with the customer’s ID, the transaction date, the total spending, and the next rank they could achieve.
  10. If the customer is already at the highest rank, inserts a new row into the “Point balance” table with the customer’s ID, the transaction date, the total spending, and a NULL value for the next rank.
  11. Closes the cursor and deallocates it.

Overall, this trigger is designed to automatically update a customer’s rank and point balance based on their transaction history.

-- Circle K database
CREATE DATABASE CircleK;

-- Access CircleK database
USE CircleK
GO;

-- TERMINAL table
CREATE TABLE Terminal (
	terminal_id VARCHAR(30) NOT NULL,
	terminal_name VARCHAR(30) NOT NULL,
CONSTRAINT PKterminal PRIMARY KEY (terminal_id)
);


-- TERRITORY table
CREATE TABLE Territory (
	zip_code VARCHAR(20) NOT NULL,
	city VARCHAR(20) NOT NULL,
	description VARCHAR(50),
CONSTRAINT PKzipcode PRIMARY KEY (zip_code)
);


-- STORE table
CREATE TABLE Store (
	store_id VARCHAR(30) NOT NULL,
	address VARCHAR(50) NOT NULL,
	zip_code VARCHAR(20) NOT NULL,
	total_width FLOAT,
	total_length FLOAT,
	total_area FLOAT,
CONSTRAINT FKzipcode FOREIGN KEY (zip_code) REFERENCES Territory
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT PKstore PRIMARY KEY (store_id)
);


-- PAYMENT TABLE
CREATE TABLE Payment (
	payment_id VARCHAR(20) NOT NULL,
	payment_name VARCHAR(20) NOT NULL,
	category VARCHAR(20) NOT NULL,
	description VARCHAR(50),
CONSTRAINT PKpayment PRIMARY KEY (payment_id)
);


-- EMPLOYEE TABLE
CREATE TABLE Employee (
	employee_id VARCHAR(20) NOT NULL,
	working_date DATE NOT NULL,
	full_name VARCHAR(50),
	gender varchar(10) NOT NULL,
	birthday DATE,
	phone VARCHAR(15) NOT NULL,
	email VARCHAR(30) NOT NULL,
	certify_no VARCHAR(20) NOT NULL,
	full_current_address VARCHAR(30) NOT NULL,
	full_permanent_address VARCHAR(30) NOT NULL,
	zip_code VARCHAR(20) NOT NULL,
CONSTRAINT CKgender CHECK (gender IN ('Male', 'Female', 'Other')),
CONSTRAINT FKzip_code FOREIGN KEY (zip_code) REFERENCES Territory
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT PKemployee_id PRIMARY KEY (employee_id)
);


-- TITLE TABLE
CREATE TABLE Title (
	employee_id VARCHAR(20) NOT NULL,
	version_no VARCHAR(20) NOT NULL,
	begin_date DATE NOT NULL,
	end_date DATE NOT NULL,
	title VARCHAR(20) NOT NULL,
CONSTRAINT PKemp_version_id PRIMARY KEY (employee_id, version_no),
CONSTRAINT FKemp_id FOREIGN KEY (employee_id) REFERENCES Employee
	ON UPDATE CASCADE
	ON DELETE CASCADE
);


-- SUPPLIER TABLE
CREATE TABLE Supplier (
	supplier_id VARCHAR(20) NOT NULL,
	supplier_name VARCHAR(20) NOT NULL,
	supplier_origin VARCHAR(20),
CONSTRAINT PKsupplier_id PRIMARY KEY (supplier_id)
);


-- SUBCATEGORY TABLE
CREATE TABLE SubCategory (
	sub_category_id VARCHAR(20) NOT NULL,
	sub_category_name VARCHAR(20) NOT NULL,
	category VARCHAR(20),
CONSTRAINT PKsub_category_id PRIMARY KEY (sub_category_id)
);

-- PRODUCT TABLE
CREATE TABLE Product (
	product_id VARCHAR(20) NOT NULL,
	product_name VARCHAR(30) NOT NULL,
	unit_price FLOAT NOT NULL,
	description VARCHAR(50),
	sub_category_id VARCHAR(20) NOT NULL,
	supplier_id VARCHAR(20) NOT NULL,
CONSTRAINT PKproduct_id PRIMARY KEY (product_id),
CONSTRAINT FKsub_category_id FOREIGN KEY (sub_category_id) REFERENCES SubCategory
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT FKsupplier_id FOREIGN KEY (supplier_id) REFERENCES Supplier
	ON UPDATE CASCADE
	ON DELETE CASCADE
)


-- PROMOTION TABLE
CREATE TABLE Promotion (
	promotion_id VARCHAR(20) NOT NULL,
	promotion_name VARCHAR(40) NOT NULL,
	start_date DATE NOT NULL,
	end_date DATE NOT NULL,
	promotion_type VARCHAR(20) NOT NULL,
	discount VARCHAR(10) NOT NULL,
CONSTRAINT PKpromotion_id PRIMARY KEY (promotion_id),
CONSTRAINT CKpromotion_type CHECK (promotion_type IN ('discount', 'buy 1 get 1'))
)


-- MEMBERSHIP TABLE
CREATE TABLE Membership (
	rank_id VARCHAR(20) NOT NULL,
	rank_name VARCHAR(20) NOT NULL,
	total_spending FLOAT NOT NULL,
CONSTRAINT PKrank_id PRIMARY KEY (rank_id)
)


-- CUSTOMER TABLE
CREATE TABLE Customer (
	customer_id VARCHAR(20) NOT NULL,
	created_date DATE NOT NULL,
	full_name VARCHAR(20) NOT NULL,
	gender VARCHAR(10) NOT NULL,
	birthday DATE,
	phone VARCHAR(15) NOT NULL,
	email VARCHAR(30) NOT NULL,
	certify_no VARCHAR(20) NOT NULL,
	zip_code VARCHAR(20) NOT NULL,
	current_rank VARCHAR(20) NOT NULL,
CONSTRAINT PKcustomer_id PRIMARY KEY (customer_id),
CONSTRAINT CKgender_cus CHECK (gender IN ('Male', 'Female', 'Other')),
CONSTRAINT FKzip_code_cus FOREIGN KEY (zip_code) REFERENCES Territory
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT FKcurrent_rank FOREIGN KEY (current_rank) REFERENCES Membership
	ON UPDATE CASCADE
	ON DELETE CASCADE,
)
-- POINT BALANCE TABLE
CREATE TABLE [Point balance] (
	customer_id VARCHAR(20) NOT NULL,
	update_date DATETIME NOT NULL,
	total_spending FLOAT NOT NULL,
	next_rank VARCHAR(20),
CONSTRAINT PKcust_update_date PRIMARY KEY (customer_id, update_date),
CONSTRAINT FKcustomer_id FOREIGN KEY (customer_id) REFERENCES Customer
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT FKnext_rank FOREIGN KEY (next_rank) REFERENCES Membership
)


-- TRANSACTION TABLE
CREATE TABLE [Transaction] (
	transaction_id VARCHAR(20) NOT NULL,
	transaction_date DATETIME NOT NULL,
	store_id VARCHAR(30) NOT NULL,
	employee_id VARCHAR(20) NOT NULL,
	terminal_id VARCHAR(30) NOT NULL,
	customer_id VARCHAR(20), -- customer_id can be NULL because there are Customers do not register membership
	total_quantity INT NOT NULL,
	total_amount FLOAT NOT NULL,
	VAT FLOAT,
	net_amount FLOAT NOT NULL,
	payment_id VARCHAR(20) NOT NULL,
	cash_received FLOAT NOT NULL,
	change_due FLOAT NOT NULL,
CONSTRAINT PKtransaction_id PRIMARY KEY (transaction_id),
CONSTRAINT FKstore_id_trans FOREIGN KEY (store_id) REFERENCES Store
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT FKemployee_id_trans FOREIGN KEY (employee_id) REFERENCES Employee,
CONSTRAINT FKterminal_id_trans FOREIGN KEY (terminal_id) REFERENCES Terminal
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT FKcustomer_id_trans FOREIGN KEY (customer_id) REFERENCES Customer,
CONSTRAINT FKpayment_id_trans FOREIGN KEY (payment_id) REFERENCES Payment
	ON UPDATE CASCADE
	ON DELETE CASCADE
)


--TRANSACTION DETAIL TABLE
CREATE TABLE [Transaction detail] (
	transaction_id VARCHAR(20) NOT NULL,
	ordinal_number VARCHAR(20) NOT NULL,
	product_id VARCHAR(20) NOT NULL,
	quantity INT NOT NULL,
	original_total_amount FLOAT NOT NULL,
	promotion_1 VARCHAR(20),
	promotion_2 VARCHAR(20),
	total_amount FLOAT NOT NULL
CONSTRAINT PKtransaction_ordnum PRIMARY KEY (transaction_id, ordinal_number),
CONSTRAINT FKproduct_id FOREIGN KEY (product_id) REFERENCES Product
	ON UPDATE CASCADE
	ON DELETE CASCADE,
CONSTRAINT FKpromoption_1 FOREIGN KEY (promotion_1) REFERENCES Promotion
	ON UPDATE NO ACTION
	ON DELETE NO ACTION,
CONSTRAINT FKpromoption_2 FOREIGN KEY (promotion_2) REFERENCES Promotion
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
);



USE CircleK
GO

-- TRIGGER THAT GENERATE DATA FOR POINT BALANCE
CREATE TRIGGER trg_ManagePointBalance
	ON [Transaction]
	AFTER INSERT
AS BEGIN

	DECLARE @customer_id VARCHAR(20)
	DECLARE @current_rank VARCHAR(20)
	DECLARE @rank_name VARCHAR(20)
	DECLARE @total_spending FLOAT
	DECLARE @next_rank_spending FLOAT
	DECLARE @next_rank VARCHAR(20)
	DECLARE @next_rank_after_update VARCHAR(20)
	DECLARE @update_date DATETIME

	DECLARE fetch_data CURSOR FOR SELECT transaction_date, customer_id FROM inserted;
	OPEN fetch_data;
	FETCH NEXT FROM fetch_data INTO @update_date, @customer_id;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT
			@current_rank = current_rank
		FROM Customer
		WHERE @customer_id = customer_id;

		SELECT
			@rank_name = rank_name
		FROM Membership
		WHERE @current_rank = rank_id;

		SELECT @total_spending = SUM(net_amount)
		FROM [Transaction]
		WHERE customer_id = @customer_id;

		IF @rank_name != 'Diamond'
			BEGIN
				SET @next_rank = CAST(@current_rank AS INT) + 1
				SELECT @next_rank_spending = total_spending FROM Membership WHERE @next_rank = rank_id
				IF @total_spending >= @next_rank_spending
					BEGIN
						UPDATE Customer
						SET current_rank = @next_rank
						WHERE customer_id = @customer_id;
						SET @next_rank_after_update = CAST(@next_rank AS INT) + 1
						INSERT INTO [Point balance] VALUES(@customer_id, @update_date, @total_spending, @next_rank_after_update);
					END
				ELSE
					BEGIN
						INSERT INTO [Point balance] VALUES(@customer_id, @update_date, @total_spending, @next_rank);
					END
			END
		ELSE IF @rank_name = 'Diamond'
			BEGIN
				INSERT INTO [Point balance] VALUES(@customer_id, @update_date, @total_spending, NULL);
			END;

		FETCH NEXT FROM fetch_data INTO @update_date, @customer_id;
	END
	CLOSE fetch_data;
	DEALLOCATE fetch_data;
END

Conclusion

The project has taken us through the entire process of designing an operational database. We began by creating an Entity Relationship Diagram during the Conceptual Modelling phase, which captured all the business requirements of the clients. We then moved on to the Logical Modelling phase where we converted the ERD into a normalized relational database that adheres to the rules of Third Normal Form. Additionally, we also created a SQL trigger to automatically accumulate points and manage membership rankings for customers with each inserted transaction. The last step in the process of database design is the Physical Modelling phase, which focuses on the performance and ACID properties of the database. As this step requires technical expertise, it is beyond the scope of this small project. Nevertheless, this project has provided an opportunity to learn many new things about database design. Based on these accomplishments, it is clear that the project has been successful in teaching valuable skills in database design.