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’;
Made with Slides.com