Tables, keys and relationships

A table holds one kind of thing. Customers in one, orders in another, products in a third. The commonest beginner design puts everything in one wide table, and it works until the first change.

A primary key identifies a row uniquely and never changes. Use a surrogate key, an id the database generates, rather than something meaningful. Email addresses change, CNIC numbers get corrected, phone numbers move. A key that can change is a key that will break every reference to it.

A foreign key points at another table's primary key. An order holds a customer id. This is what makes the relationship real, and declaring it lets the database refuse an order for a customer who does not exist. That refusal is a feature: without it you will accumulate orphan rows and only discover them when a report does not add up.

Three kinds of relationship. One to many is most of them, and the foreign key goes on the many side: an order has one customer, a customer has many orders. Many to many needs a third table holding both keys, such as students and courses with an enrolments table between them. One to one is rare and usually means the two tables should be one.

Choose types deliberately. Money is never a floating point number, because rounding errors accumulate and accounts stop balancing; use a decimal type with explicit precision. Store dates as dates, not text. And store a phone number as text, because it is not a quantity and leading zeros matter.