Monica Wei

If I were hired as a database designer for a small local toy shop, I would create several essential tables, such as CUSTOMERS, ORDERS, ORDER_DETAILS, TOYS and EMPLOYEES. The ORDERS table would serve as the main table that connects most of the others. The design should take into account two different order types: online and offline.

For example:

The columns for the CUSTOMERS table can be:

CustomerID (Primary Key)

Username

PasswordHash (optional）

Gender(optional)

DateOfBirth (optional)

Email

ShippingAddress

PhoneNumber

This table is primarily intended for online customers, as such detailed personal information is usually not collected during offline purchases.

The columns for the ORDERS table are:

OrderNumber (Primary Key )

OrderDate

CustomerID (Foreign Key)

TotalPrice

EmployeeID (Foreign Key)

OrderStatus

OrderType( Offline/Online)

This table is designed to accommodate both online and offline orders.

For offline orders, the EmployeeID field indicates the staff member who processed the order.
For online orders, the OrderStatus field (e.g., Pending, Shipped, Delivered) is used to track order progress.
The OrderType field helps distinguish between the two types of transactions.

Customers may buy different types of toys, so it’s better to have a ORDER_DETAILS table.

The columns for the ORDER_DETAILS table are:

OrderNumber (Foreign Key )

ToyID (Foreign Key)

UnitPrice

Quantity

LineTotal

Here, the ORDER_DETAILS table can have a composite primary key made up of OrderNumber and ToyID. This table is suitable for both online and offline orders.

The columns for the TOYS table are:

ToyID (Primary Key)

EntryDate

SoldDate

StockQuantity

SupplierID(Foreign Key)

PurchasePrice

This table is suitable for both online and offline orders.

The columns for the EMPLOYEES table are:

EmployeeID (Primary Key)

FirstName

LastName

Gender

DateofBirth

Email

Address

PhoneNumber

HireDate

EndDate

This table does not connect directly to online purchases.

I just have listed a few essential tables. More tables should be considered either now or in the future.

For example:

The SUPPLIERS table;

The SHIFT_SCHEDULING table;

The RETURNS_AND_EXCHANGES table;

The INVENTORY_ALERTS table;

The ORDER_TRACKING table;

The TOYCATEGORIES table;

…

How to connect these tables is a core question, involving various attributes, primary keys and foreign keys. The design process should follow the Database Life Cycle (DBLC) principles and also conform to the System Development Life Cycle (SDLC). It is an evolving process — it can be simpler at the beginning and does not need to be overly complex. As the business grows, more considerations will arise. Factors such as cost, performance and data integrity are equally important.