Build an eCommerce Website with PHP – Part 3 – Database Design

database

Database Design

Oracle SQL Server is a Database Management Software produced and marketed by the Oracle Corporation. It uses familiar SQL Syntax to interact with the database and can be used with a very large number of programming languages including PHP.

Although commonly used in enterprise applications, the Oracle SQL Server offers adequate functionality so that even a single developer can set it up and get a website up and running in no time.

For this project I used the free version available on the market known as Oracle Express Edition or Oracle XE. This version has many the features present in the commercial version but is limited to 10GB of user data, 1GB of database data and can only run on a single CPU.

These limitations however are way above what this project requires.

Designing the database

I designed the database from the start as one for an online shop, even though the client at first was against having a shopping cart or orders stored in the system. This proved to be a good decision and as the site grew it required more and more functionality until it became a fully functional ecommerce application.

The database, however only grew in size, its schema has remained largely unchanged. This proves that good database design needs to come before good web development.

There are 5 major tables in my database design, they are:

  1. Categories – contains all of the product categories in the site
  2. Products – contains all of the products that are in the store
  3. Messages – contains messages sent to the site admin by customers, this acts as backup as these messages are also sent via email
  4. Users – contains all of the registered users of the site
  5. Orders – contains all of the orders that have been placed
Diagram of the DB tables

Diagram of the DB tables

SQL code

The SQL code used to generate the database is listed below.

CREATE TABLE categories (
name varchar(50) NOT NULL,
parent varchar(50) DEFAULT NULL,
type int NOT NULL,
description varchar(300) DEFAULT NULL,
status int DEFAULT 0,
position int NOT NULL,
posted_date date NOT NULL,
created date NOT NULL,
created_pk varchar(35) NOT NULL,
created_ip varchar(15) NOT NULL,
updated date NOT NULL,
updated_pk varchar(35) NOT NULL,
updated_ip varchar(15) NOT NULL,

constraint category_pk primary key (name)
);

drop table products;
CREATE TABLE products (
id int NOT NULL,
category_pk varchar(50) NOT NULL,
title varchar(100) NOT NULL,
photo varchar(200) ,
desc1 varchar(100) NOT NULL,
desc2 varchar(100) DEFAULT NULL,
desc3 varchar(100) DEFAULT NULL,
desc4 varchar(100) DEFAULT NULL,
desc5 varchar(100) DEFAULT NULL,
desc6 varchar(100) DEFAULT NULL,
desc7 varchar(100) DEFAULT NULL,
desc8 varchar(100) DEFAULT NULL,
desc9 varchar(100) DEFAULT NULL,
desc10 varchar(100) DEFAULT NULL,
price varchar(10) NOT NULL,
sale varchar(100) DEFAULT NULL,
pricesale varchar(10) DEFAULT NULL,
posted_date date NOT NULL,
created date NOT NULL,
created_pk varchar(35) NOT NULL,
created_ip varchar(15) NOT NULL,
updated date NOT NULL,
updated_pk varchar(35) NOT NULL,
updated_ip varchar(15) NOT NULL,

constraint products_pk primary key (id)
);

drop table messages;
CREATE TABLE messages (
email varchar(50) NOT NULL,
phone varchar(15),
subject varchar(200),
message varchar2(4000),
response varchar2(4000),
status int default 0,
posted_date date default sysdate,
created date default sysdate,
created_pk varchar(35) DEFAULT NULL,
created_ip varchar(15) NOT NULL,
updated date default sysdate,
updated_pk varchar(35) DEFAULT NULL,
updated_ip varchar(15) NOT NULL,
);

drop table users;
CREATE TABLE users (
email varchar(35) NOT NULL,
password varchar(40) NOT NULL,
fname varchar(255) not null,
lname varchar(255) not null,
phone varchar(255) not null,
address varchar2(255),
state varchar2(255),
city varchar2(255),
code varchar2(255),
role varchar(35) default 'user',
status int DEFAULT '0',
visits int DEFAULT '0',
last_login date DEFAULT sysdate,
created date default sysdate,
created_ip varchar(15) NOT NULL,
updated date default sysdate,
updated_ip varchar(15) NOT NULL,
updated_pk varchar(35) NOT NULL,

constraint users_pk primary key (email)
);

drop table orders;
create table orders(
email varchar2(255) not null,
first_name varchar2(255) not null,
last_name varchar2(255) not null,
address varchar2(255) not null,
phone varchar2(255) not null,
state varchar2(255) not null,
city varchar2(255) not null,
code varchar2(255) not null,
cart varchar2(2048) not null,
shipped varchar(5) default 'false',
shipped_date date default null,

created date default sysdate,
updated date default sysdate
);
Series Navigation