Requirements Gathering Example: Part 2, Database Schema

In Part 1, we outlined the process of what it takes to gather information to build our system and database for our online store. In this article, we showcase a high level overview of our database schema.

Designing a database schema for an online merchandise store involves defining the structure of the database to efficiently store and manage information about products, customers, orders, and other relevant entities. Below is a simplified example of a database schema for an online merch store:

1. Products Table:
   • ProductID (Primary Key)
   • Name
   • Description
   • Price
   • StockQuantity
   • CategoryID (Foreign Key referencing Categories table)
2. Categories Table:
   • CategoryID (Primary Key)
   • CategoryName
3. Customers Table:
   • CustomerID (Primary Key)
   • FirstName
   • LastName
   • Email
   • Password
   • Address
   • Phone
4. Orders Table:
   • OrderID (Primary Key)
   • CustomerID (Foreign Key referencing Customers table)
   • OrderDate
   • TotalAmount
5. OrderDetails Table:
   • OrderDetailID (Primary Key)
   • OrderID (Foreign Key referencing Orders table)
   • ProductID (Foreign Key referencing Products table)
   • Quantity
   • Subtotal

This schema represents a basic structure. Depending on the specific requirements of your online merch store, you may need to expand or modify the schema. Additional considerations may include:

• Images: If your products have images, you might want to include a field for image URLs or store images in a separate table.
• Reviews: If you allow customers to leave reviews, you may want a table to store review information, linked to the Products or Orders table.
• Promotions and Discounts: If your store offers promotions or discounts, you might need additional tables to handle this information.
• Shipping Information: If your store deals with physical products, you may need to store information about shipping, such as shipping address, tracking numbers, etc.

Remember to establish relationships between tables using foreign keys to maintain data integrity. Normalization techniques can also be applied to avoid data redundancy.

After designing our schema, we can continue and create our ER diagrams to get a visual representation of our database based on the information we put together.

Finally, after that process we can go ahead and create our physical database on the actual servers or cloud, to get our system up and running.

This is a general guide, and the actual schema will depend on the specific features and requirements of your online merch store.