Data modelling for MySQL

gistfile1.txt

What is a Relational Database?
------------------------------

A set of true propositions
Every row in a table is the statement about the truth related to the table
Enforces logical integrity (business rules)
Application independent store (this is the reason why relational database was created)

Why Normalize?
--------------

Remove redundancy
Avoid wrong or contradictory query results
Safety with integrity constraints
Avoid loss of information

What is normal?
---------------

All attributes are dependent on the key, the whole key, and nothing but the key

Not 1NF
-------

Repeating groups, repeating fields, or multivalued columns

Not 2NF
-------

Non-key dependent on part of key
Repitition (redundancy)

Not 3NF
-------

Non-key field dependent on non-key field

Enforce Foreign Keys
--------------------

Joins will return what you expect
And there is an insert cost

Surrogate Keys vs Natural Keys
------------------------------

Name is a natural key
ID is a surrogate key

Avoid NULL
----------

They always require special handling
Three valued logic
What is the semantic meaning? (Unknown/Default/Missing/Not Applicable)

Physical Issues (Indexes, Partioning, Sharding)
-----------------------------------------------

Performance tuning is a whole arts and science of itself
Normal means truth, correctness
Performance is physical, not logical
Surrogate keys are preferre (But, ensure there exists a natural candidate key)
Avoid NULLs