A database schema organizes entities, attributes, and their relationships into a data model. Treating schema design as personal judgment may seem practical, but different experience levels produce inconsistent structures, duplication, and performance problems. section 4.1
Avoid these common schema-design mistakes:
- Mistake 1—assuming a sensible-looking design is normalized. Use an Entity Relationship Diagram (ERD) to identify entities, attributes, and relationships before implementation. section 4.1
- Mistake 2—storing compound values in one attribute. A customer name such as Peter A. Carter should use separate first-name, middle-initial, and last-name values when those parts retain meaning independently. section 4.1.2
- Mistake 3—repeating category data for every subcategory. Duplication wastes space, increases table size and memory use, reduces index efficiency, and makes inserts and updates less efficient. section 4.1.2
- Mistake 4—letting incremental development replace architecture. Design sprints can review each database schema before accumulated changes create a tangled, duplicated structure. section 2.2
The MagicChoc naming trap
Ambiguous names such as @Address, @date, and @Product obscure whether values represent billing addresses, delivery addresses, order dates, products, or quantities. Replacing them with @BillingAddressID, @DeliveryAddressID, @OrderDate, @ProductID, and @OrderQty clarifies the schema and keeps meaning consistent through procedures, tables, XML, and downstream calls. section 2.2
SELECT SUBSTRING(CustomerName, 0, CHARINDEX(' ', CustomerName, 1))
FROM Customers ;
Inspecting a query that extracts a first name from CustomerName makes the cost of non-atomic schema values immediately visible. section 4.1.2
