A relational database stores data in tables, also called relations. Each table describes one kind of thing, such as customers, products or orders. Each row is one instance of that thing, and each column is an attribute with a defined data type. Most business systems, including sales, finance and HR applications, keep their data in relational databases such as PostgreSQL, MySQL, SQL Server or Oracle, and you query them with SQL.
A primary key is the column, or combination of columns, that uniquely identifies each row. It cannot be null and cannot repeat. CustomerID in a Customers table is a typical primary key. A key built from two or more columns is a composite key, for example OrderID plus LineNumber in an order-lines table. A natural key comes from the business (a tax ID); a surrogate key is an artificial number the database generates, which stays stable even if business values change.
A foreign key is a column in one table that refers to the primary key of another table. Orders.CustomerID points to Customers.CustomerID. The database can enforce referential integrity: it refuses an order for a customer who does not exist and can block deleting a customer who still has orders. Relationships have a cardinality. One-to-many is the most common (one customer, many orders). One-to-one is rarer (a person and a passport record). Many-to-many (students and courses) needs a junction table that holds both keys.
Normalization is the process of organizing tables to reduce redundancy and update problems. In first normal form (1NF), each column holds one atomic value, with no lists in a cell and no repeating column groups. In second normal form (2NF), every non-key column depends on the whole primary key, not part of a composite key. In third normal form (3NF), non-key columns depend only on the key, not on other non-key columns. A customer's city should not be stored on every order row; it belongs in the Customers table once.
Normalized designs suit transactional systems because each fact is stored once, so updates are fast and consistent. The cost is that analysis needs many joins. That is why reporting systems often denormalize, copying descriptive attributes into wider tables to make queries simpler and faster. Neither is wrong: the right design depends on whether the workload is writing transactions or reading for analysis.
Key terms
- Primary key
- A column or set of columns whose values uniquely identify each row and are never null.
- Foreign key
- A column that refers to another table's primary key, linking the tables and enforcing referential integrity.
- Normalization
- Organizing tables into normal forms (1NF, 2NF, 3NF) so each fact is stored once, reducing redundancy and update anomalies.
- Junction table
- A table holding pairs of foreign keys that resolves a many-to-many relationship.
A small shop kept orders in one spreadsheet with the customer's name, email and address repeated on every row. When a customer changed email, some rows were updated and others were not. Moving to a Customers table (CustomerID primary key) and an Orders table with a CustomerID foreign key means the email is stored once and every order links to the current value.
Check yourself
How do you model a many-to-many relationship between students and courses?
Create a junction table such as Enrollments with StudentID and CourseID foreign keys (together often forming a composite primary key).
Why might a reporting database deliberately denormalize data?
Fewer joins make analytic queries simpler and faster, and reporting systems are read-heavy, so the redundancy is an acceptable trade-off.