BuildBright Database Design
Business Objective
BuildBright Home Supply sells tools, painting supplies, and lawn and garden products. The database stores customer information, product details, current inventory quantities, and sales records. This information can be used to review purchases, check stock, and calculate sales totals.
Tables and Relationships
- Customers: Stores customer names, addresses, and emails. CustID is the primary key.
- Products: Stores product names, categories, descriptions, prices, and stock quantities. ProductID is the primary key.
- Sales: Stores the customer, product, quantity, price paid per unit, and sale date. SaleID is the primary key. CustID and ProductID are foreign keys.
One customer can have many sales, and one product can appear in many sales. Each sale records one product type and the quantity purchased. This version does not group multiple different products into one order.
E-R Diagram
Click the diagram to view it at full size.
Business Rules
Each record has a unique ID. Every sale must reference an existing customer and product. Foreign keys use RESTRICT to prevent deleting referenced customers or products. The unit price is stored with each sale to preserve the purchase price when catalog prices change.
Purchase quantities must be positive, and prices and stock quantities must not be negative. These rules need validation when adding or updating records. Recording a sale does not automatically reduce inventory in this version.
Database Testing
A query joining Customers, Products, and Sales returned four sample sales with the correct customer and product names. Multiplying quantity by unit price produced totals of $59.99, $59.98, $14.99, and $38.97.