Relational database design

Plan your db

What information must be stored?

In which tables? 

In what form?

Goals of db design

Minimum redundancy

Integrity (Relation and data) 

Performance

Normalization

• Minimize redundancy
• No anomalies
  • update - same data in two table
  • delete - only one record 
  • insert - inserting one attribute requires inserting additional info

Keys

• Unique
• Primary
• Foreign
• Composite
• Candidate

Relations

• One to One
• One to Many
• Many to Many

Joins

• Inner join - Only when there is a match
• Left join - all from first table even if there are no matches in second table
• Right join - all from second table even if there are no matches in first table
• Outer join - retrieves data from both the tables, when there are no matching 

Best practices

• Comment
• Don't store binary data
• Same column in different tables should have same datatype (e.g foreign key)
• no select *
• When running an insert action query, use the column list into which to insert instead of the table name

Why naming convention

• Clear structure
• Everyone is on same page
• Maintenance

General conventions

• Lower case only
• Separate names by underline never by spaces or dot
• Don't use numbers in the name
• No reserved words, list of MYSQL reserved words.
• No awkward names - ex. dsc_pr
• Use project name as the name of database
• Prefix the DB name with the environment (proto and prod)

Table Name

• Name of the entity in plural
• Prefix tables name with a three letter keyword to which it is related (e.g stl_projects, stl_timers etc)
• All users table should be prefixed with user_ (e.g user_timer, user_project_decisions)

Column Name

• id for primary keys (prj_id)
• prefix column names with three letter table acronym
• foreign key should have id, prj_stl_id (refers to project table, belongs to storyline table and is not a key)

Security

• SQL injection - more here

SELECT fieldlist
FROM table
WHERE field = ‘'anything' OR 1=1’;