Lesson 6: Designing the Schema: Tables, Rows, and Columns
Effective database design starts with identifying the entities you need to store and defining their attributes.
Identifying Entities and Tables
An Entity is a real-world object that needs to be represented in the database (e.g., a Customer, a Product, an Order).
- Each entity becomes a Table.
Defining Attributes and Columns
Attributes are the properties that describe an entity (e.g., a Customer has a Name, an Address, and an Email).
- Each attribute becomes a Column.
Choosing Data Types (Domains)
Each column must have a specific data type to ensure the correct kind of data is stored, which is vital for integrity and memory management.
| Type | Description | Example SQL Type (Common) |
|---|---|---|
| Numeric | Whole numbers or decimal values. | INTEGER, DECIMAL, FLOAT |
| String/Text | Letters, words, and alphanumeric characters. | VARCHAR(N), TEXT |
| Date/Time | Temporal values. | DATE, TIME, DATETIME |
| Boolean | True or False values. | BOOLEAN or TINYINT (0 or 1) |
Example: Designing the 'Products' Table
We need to store data about products.
| Attribute (Column Name) | Data Type | Constraint Notes |
|---|---|---|
| ProductID | INTEGER | Unique identifier |
| Name | VARCHAR(100) | Product name |
| Price | DECIMAL(10, 2) | Price with 2 decimal places |
| StockQuantity | INTEGER | How many are in stock |
By carefully choosing the data types, we prevent errors like putting text in the Price column.