Development of database and stored procedures for E-commerce systems
Copyright ⓒ The Korean Society of Marine Engineering
This is an Open Access article distributed under the terms of the Creative Commons Attribution Non-Commercial License (http://creativecommons.org/licenses/by-nc/3.0), which permits unrestricted non-commercial use, distribution, and reproduction in any medium, provided the original work is properly cited.
Abstract
Nowadays, e-commerce is an important entity in our everyday life. To build such e-commerce systems effectively, large amounts of data need to be managed and a large number of application programs needs to be developed. To alleviate this burden, this work proposes new designs for database and stored procedures. The database design focuses on a simple but an efficient storage of e-commerce-related data. On top of the database, stored procedures were designed to reduce the burden of application programming. The stored procedures were categorized into basic and advanced stored procedures. To show the applicability of the proposed designs for creating e-commerce systems, a simple website was developed and evaluated.
Keywords:
E-commerce, Database, Stored procedure1. Introduction
E-commerce has become an important entity in our daily life. More and more companies are adopting the e-commerce system to improve their existing businesses. The importance of e-commerce is reflected by the fact that there were 1.66 billion online buyers in 2017. In addition, a case study reported that mobile commerce would make up to 45% of the total e-commerce revenue by 2020. To gain comparative advantage in the business world, most companies need to adopt the e-commerce way of doing their businesses [1]-[5].
E-commerce has the potential to drive the economy of a country to the next level with assured online business and enhanced profitability. Despite the advantages of e-commerce for business owners and customers, the building of an e-commerce system or application for a business is challenging.
The creation of an e-commerce system starts with the building of a website or application that can promote the business. The website or application must have a good foundation and should fulfil the needs of the business. To achieve this, there are a few important aspects in terms of application creation, two of which are database and stored procedure.
A database is an important aspect of any application. The design of such a database for any given application is becoming more complicated even for experienced programmers [6]-[8]. In addition, a guide or process to design a database for e-commerce using the stored procedure technique is needed to further improve the application. Stored procedures may have many advantages for the development of applications [9]-[11].
The research objectives of this study are as follows.
- 1) Preparing a base framework for database creation in the e-commerce sector: With the growth of e-commerce applications, a framework is required to create databases, which will help programmers or designers in creating their own databases for e-commerce applications.
- 2) Stored procedure design for e-commerce application: Stored procedure is an important technique to improve the performance of e-commerce applications. A design that can be used as a guide is also important for helping programmers. The stored procedure design of this study may be used in the creation of e-commerce applications.
- 3) Testing the effectiveness of the stored procedure for an e-commerce system: This study tests the effect of using stored procedures in developing e-commerce applications. The results show that they may improve the effectiveness of the e-commerce systems.
2. Review of E-commerce, Database, and Stored Procedure
2.1 E-commerce
E-commerce means electronic commerce. Any business performed over the internet is considered e-commerce. E-commerce is usually related to a website in the internet, which offers products or services from the portal. The transformation, creation, and redefining of relationship between an organization and individuals through electronic communication and digital information exchange is also e-commerce.
E-commerce is changing the way a business approaches its customers to sell its products and services. By the virtue of internet, businesses and customers have become more connected. Nowadays, more and more businesses are using e-commerce as a means of providing services and products. With the rapid devolvement of communication technologies, e-commerce will be the future of business.
E-commerce has achieved a sales growth of 18% in 2018. The global consumer purchases over the internet were approximately $2.86 trillion in 2018 compared with $2.43 trillion in 2017. This shows the amazing growth and importance of the e-commerce market. Furthermore, some companies have started adopting multiple digital channels to promote their businesses. A Forbes case study reported that businesses that adopt multiple digital channels outperform the businesses that adopt single or dual channels by 300% [3]. This shows the significance of e-commerce in today’s growing business.
2.2 Database and Stored Procedure
A database is a collection of information that is organized for easy access, management, and updating. A database is an important aspect for creating any type of system. Database design affects the system significantly and plays an important role in e-commerce applications.
A database is organized into rows, columns, and tables, and is indexed to ensure fast information retrieval. New information can be added to a database, and data can be updated and deleted. With the help of Structured Query Languages (SQL), databases have become more manageable and easier to control [6]-[8].
Figure 1 shows a database design for e-commerce proposed by Song and Whang [12]. The design shows the important components that are required in an e-commerce application. The application programmer may change the design depending on the need of the application. The design shows how the databases are linked to each other. Some parts can be combined as a sector or group, for example the inventory and catalogue.
Database design example for e-commerce [12]
A stored procedure is a program written in procedure and trigger language that is stored as a part of the database. Stored procedures can be called by client applications, other stored procedures, or triggers. Triggers are almost the same as stored procedures with one exception, i.e., the way they are called. Triggers are called automatically when a change occurs in the row of a database table [9]-[11].
This study examines the stored procedures first followed by triggers. As we will see, most aspects that are applicable to stored procedures are also applicable to triggers. A stored procedure can also access or modify data in a database; however, it is not tied to a specific database or object.
Figure 2 shows the interaction of a stored procedure with applications and databases. When a stored procedure is used, any information that is needed by an application from a database goes through the stored procedure. A stored procedure acts as a middleware between the application and database, and can control the flow of data entering and exiting the database. Stored procedures can improve the performance, reliability, and maintainability of any database application.
Working of stored procedure
3. Database Design and Stored Procedures
3.1 Database Design
This study proposes a new database design that focuses on the simplicity and comfort of use. The database is divided into a few sections.
As shown in Figure 3, the database design consists of five sections, which are user section, inventory section, order section, payment section, and delivery section. Each section has its own database tables, and each table has its own use. A database is divided into sections to provide a better understanding of the database and for ease of use.
Overall structure of a database
As shown in Figure 4, the user section is designed to be used by both the user and admin of the e-commerce system. It consists of four tables, which are user_account, user, address, and addr_state.
User section
The user_account table contains the user login data, that is, id and password. The user table contains user data that are required by the e-commerce system. This table may be changed appropriately based on the needs of a specific e-commerce application. The address table is used to store the addresses of users. It is designed as a separate table to have more details of the address and to provide the admin with more control. The addr_state table contains the state of an address.
The inventory section (Figure 5) is not only one of the important sections in database design, but also one of the most used sections. This section is expected to have a higher user traffic compared with the other sections. The inventory section consists of shop_cart, inventory, inventory_images, inventory_category, and event.
Inventory section
The inventory table is the main table for the e-commerce application. All the inventory or product information is updated here for the application. The inventory is divided into category, which is inserted in the inventory_category table. This makes the category for the inventory more organized. Thus, it will be easier for a user to search for a product by inventory and will help the admin in sorting out an inventory according to the category. The images for the inventory would be stored inside the inventory_images table. Since one inventory might have more than one product image, this would further help in sorting the images for the inventory as the foreign key of the table is inv_id.
The event table is for special events regarding a product; the event might be a special offer or discount for the specific product. Events can be removed or added in this table. The shop_cart table contains temporary data that is used by the user when they are browsing the inventory. All the data are linked to the user section and order section.
As shown in Figure 6, the order section consists of three tables, which are order_hist, order, and order_detail. Users will use the order section to place orders.
Order section
The main table is the order table, which is linked to the user. Any order placed from the inventory will first go to the order table. The order detail is for the user to check any order made from the cart in the inventory section. The order history is for previous orders placed by the user in the e-commerce application. This is linked to the user section or block and other parts of the database table.
As shown in Figure 7, the payment section is where a payment is made after passing through the order section. This section consists of three tables, which are credit_card, payment, and invoice.
Payment section
The credit_card table is where the user credit card details are kept and transactions are performed. The payment table contains the records of all the payments for the user and admin and the e-commerce application for processing. An invoice is generated after the payment is made and confirmed, and is sent to the user with the user_address retrieved from the user section.
As shown in Figure 8, the delivery section consists of two tables, which are delivery and del_status. The delivery table records and checks any delivery when the delivery is made to the user. The del_status is for the admin to set the type of delivery status, for example, delivered or in process. The delivery is made only after the payment is confirmed.
Delivery section
As shown in Figure 9, the database design is focused on simplicity and connectivity between the database tables. The tables are divided into a few sections, and all the sections have their own tables.
Database tables
All the tables are linked or connected to each other by a specific stored procedure. The stored procedure controls the data flow from one table to another, and decides the data that enter a specific table.
3.2 Basic Stored Procedures
Stored procedure design can be approached in a simple way. It is a simple way to retrieve required data, and the basic designs can be used repeatedly. The design usually involves retrieving and inserting data into a database.
A basic stored procedure design normally consists of three parts for each database. These are “add data,” “edit data,“ and “delete data” from a database. A stored procedure is usually designed to be very specific to a given action. A basic stored procedure design usually involves three actions that are required in almost all databases.
Some databases or tables have the same stored procedure such as add data; however, it is designed specifically for a certain action. Stored procedures are coded in such a way that they can be used for any kind of tables depending on the specific requirement. Unlimited number of stored procedures can be stored in a database for a specific table.
The above code is an example of a basic stored procedure design. It is mainly used to update a database where it inserts the data into CategoryName. It can improve the performance in a way that the query can be repeated without typing the code repeatedly.
Another example is a simple stored procedure that can be used to update a data with a given query. Rather than entering a specific data, the stored procedure manages the data and enters the data into the table. It improves the performance of certain aspects of the DBMS.
The stored procedure query can be used to insert a data into a database. Compared with the normal method wherein data are directly inserted into the database, the stored procedure inserts the data into the database. The stored procedure activates only when needed. This gives an advantage to the programmer in that the programmer will only have to apply a specific stored procedure for a specific action. The basic stored procedure will provide more control over the program that is created by the programmer.
This is the stored procedure for the databases. Most stored procedures would have the same type of add, delete, or edit data because they are the basic operations on data. The stored procedures are created for the tables in a database.
1) add_user: This stored procedure is used to register any user to the database. It is a basic stored procedure to add data but can involve multiple tables, which are user_account, user, and address.
2) edit_user: This is used to edit any information that is already registered inside the database. Both admin and user can use this.
3) delete_user: This is used to remove any user from the database. This stored procedure can remove specific data that are linked to a specific user.
4) add_addr_state: This is used to add new state into the addr_state table.
5) delete_addr_state: Removes any data from the addr_state table.
6) add_inventory: Adds data to the inventory table as well as related images. This will mostly be used by the admin of the website.
7) delete_inventory: Deletes data from the inventory table.
8) edit_inventory: Edits specific data in the inventory table.
9) add_event: Adds any event related to the inventory. The data is inserted into the event table.
10) delete_event: Removes data from the event table.
11) add_inv_category: Adds data to the inventory_category table.
12) delete_inv_category: Removes data from the inventory_category table
13) add_shop_cart: Adds data to the shop_cart table.
14) delete_shop_cart: Clears all the current data in the shop_cart table.
15) edit_shop_cart: Edits or changes the data in the shop_cart table.
16) add_delivery_stats: Adds delivery status in the del_status table.
17) delete_delivery_stats: Removes data from the del_status table.
18) delete_delivery: Removes any data from the delivery table.
3.3 Advanced Stored Procedures
Stored procedures can be used extensively in a database, and more complex stored procedures work better. One stored procedure can be used for multiple databases, and it can act as a bridge between databases. Some of the designed stored procedures are listed below.
The code shows an example of a created stored procedure. This stored procedure retrieves data from the application and replaces them with the existing data in the database.
This is an advanced stored procedure that is implemented when a user purchases an item from the e-commerce application. It deducts an inventory value from the inventory table and retrieves the price amount of a purchased item from the order table. In addition, the item detail is retrieved from the order table and recorded inside the invoice table. With this stored procedure, a few different transactions are involved between tables.
The delivery stored procedure transfers data between the invoice, user address, and status of the delivery. When a payment is made and an invoice is created, the delivery stored procedure records the data and transfers data from the address table to the delivery table.
The updating_order stored procedure is mainly used to update any order. Any order made previously is updated from the order table to the order history table, and the information is retrieved from the order detail. Rather than making three different stored procedures for the same query, one stored procedure is made to ensure that all the transactions are performed simultaneously to avoid any mistakes.
4. Website Implementation
A website was implemented in two sites, which are the user site and admin site. The two sites have a few major differences. For example, the user site is more focused on the ease of experience and simplicity and provides the user with a more basic but necessary control over what they want to view and search. On the other hand, the admin site focuses more on detailed lists and structures. There are fewer images involved to save space and ensure that unimportant information is involved. The admin of the site will have control on most of the information displayed in the website.
Figure 10 shows the website main page. As can be seen, there is a search bar to help the user search for specific items on the site. The site does not require a user to login to use it; however, it is an option for the user. A few different stored procedures are used in the main page; for example, a stored procedure is used in the search bar. The stored procedure retrieves the data from the search bar and implements a search based on the requirement. It then displays the data on a new page with the format that is implemented by the admin. In this instance, at least one stored procedure is implemented in each button and the display of banner, and the offered items are also be involved with the stored procedure.
Main page.
As shown in Figure 11, the basic design of the website remains the same. The image is retrieved from the inv_image table that is linked to the inventory. The content is changed depending on the needs or customer choices. After the customer confirms a product, it is inserted in the shopping cart where the details are held and collected until the purchase is confirmed.
Buying a product
Figure 12 and Figure 13 show the implementation of the admin pages. The admin pages are designed for the admin to edit or remove any data easily from the database. This give the admin control on what can be viewed. In addition, this implementation allows the admin to insert any data easily into the website.
Admin main page
Adding inventory
5. Conclusion and Further Works
This work proposed a database design and stored procedures for e-commerce systems. The database is divided into five sections to modulize and maintain e-commerce systems efficiently. Stored procedures are also divided into two groups to increase their usability. By using stored procedures in e-commerce applications, the query for a specific action can be reused without creating new ones. Therefore, stored procedures may reduce the burden of constructing e-commerce systems.
To show the possibility of the proposed design, a prototype of e-commerce system was implemented. The implementation showed that e-commerce data can be managed properly, and e-commerce website can also be developed efficiently. Although the proposed design was tested through a prototype website, it needs to be verified through a commercial e-commerce system in the future.
Author Contributions
Conceptualization, H. C. Park; Methodology, H. C. Park; Software, M. H. B. Maslan; Validation, M. H. B. Maslan; Formal Analysis, M. H. B. Maslan; Investigation, M. H. B. Maslan; Resources, M. H. B. Maslan; Data Curation, M. H. B. Maslan; Writing—Original Draft Preparation, M. H. B. Maslan; Writing—Review & Editing, H. C. Park; Visualization, M. H. B. Maslan; Supervision, H. C. Park; Project Administration, H. C. Park; Funding Acquisition, H. C. Park.
References
- S. A. Bhat, K. Kansana, and J. M. Kahn, “A review paper on e-commerce,” Asian Journal of Technology & Management Research, vol. 6, no. 1, pp. 16-21, 2016.
- A. G. Khan, “Electronic commerce: A study on benefits and challenges in an emerging economy,” Global Journal of Management and Business Research, vol. 16, no. 1, pp. 18-22, 2016.
- Global Ecommerce Sales to reach nearly $3.46 Trillion in 2019, https://www.digitalcommerce360.com/article/global-e-commerce-sales, , Accessed November 13, 2019.
- U. Varshney, R. Vetter, “A framework for the emerging mobile commerce applications,” Proceedings of the 34th Hawaii International Conference on System Sciences, pp. 1-10, 2001.
- K. Hameed, K. Ahsan, and W. Yang, “Mobile commerce and applications: An exploratory study and review,” Journal of Computing, vol. 2, no. 4, pp. 110-114, 2010.
- G. Harrison and S. Feuerstein, MySQL Stored Procedure Programming, O'Reilly Media, 2006.
- H. C. Wei and T. Godfrey, “Database middleware and web services for data distribution and integration in distributed heterogeneous database systems,” Proceedings of the 2006 International Conference on Information & Knowledge Engineering, 2006.
- R. Balk, Database Programming Made Easier, Master Thesis, Electrical Engineering, Mathematics and Computer Science, University of Twente, Netherland, 2016.
- O. Omowa, Development of an E-Service App on the Android Platform, Bachelor Thesis, Information Technology, Oulu University of Applied Sciences, Finland, 2016.
- C. B. Thaden, Analysis of Multi-Platform Mobile Application Development, Master Thesis, Eletrical Engineering, University of North Dakota, USA, 2014.
- S. Meer, Middleware and Application Management Architecture, Doctoral Thesis, Eletrical Engineering, University of Berlin, Germany, 2002.
- I. Y. Song and K. Y. Whang, “Database design for real-world e-commerce systems,” IEEE Data Engineering Bulletin, vol. 23, no. 1, pp. 23-28, 2000.