Database Desigm
Minimize data reduncancy with normalization
One Primary Key must be unique, unchanging, non null
Understanding Query Optimization:
Indexes
none indexes mean full table lookups vs index that binary tree the shit, none primary key indexes create new tiny table for 2 binary tree lookups
Updating indexes takes longer
Should I use a id(surrogate key) or a unique attribute(natural key) for the primary key?
Be consistent for your entire database so people don't get confused whats the foreign key
Natural connection to real world that may change and requires some thought, natural more clear when reading in db
Relationships
Parent-child
one to many
child has foreign key that is primary key of parent
Parent is person ordering or author, child is order or book
Many to Many
Problems: Lists of ids are hard to maintain and both try to be parents
Solution: Joining/bridging/intermediate table that becomes the child of both parents. Compound/composite key here
Student
Class
3
4
3
5
5
4
Lookup Table
Now have a lu_membership lookup table, that is foreign key in member table instead of stored with member.
id | Membership |
1 | Bronze |
2 | Silver |
3 | Gold |
Foreign key constraints means all members can only have memberships that exist, updates to membership ez
Foreign Key Constraints
FK constraints refers to what happens to parents referenced by children:
on delete
on update
For each action can:
restrict(stops action)
cascade(update/delete children as well)
set null(remove foreign key reference)
Normalization
1st Normal Form: Single elements, not lists. Create new tables with a row with foreign key_id and each element of list
2nd Normal Form: Keep attributes in correct table
3rd Normal Form: Column depends on column which depends on pk. Transitive dependency: an attribute depends on anthoer attribute in the row i.e star, star meaning which depends on type. Should create new table for stars
Other
Enums
Lets say you use gender across tables like as a requirement of group membership or person
Would you make a table with
id | title |
1 | male |
2 | female |
And then reference it with foreign keys
Compared to just storing male everywhere db level enums are better b/c:
foreign key database level assurance (not for lists)
easy cross platform changes as not typed to code
Removes constants in code and puts in db
Worse b/c:
support weird/difficult seeing gender = 1
Naming
User or Users => survey says singular cuz
slightly clearer on JOINs like join on user, though worse on slect from user, but you can consider it the user table
Consistency: Plural forms vary word to word(status, news), what if each entry is plural like studentfacts facts
In alphabetical order, order then orderdetails better than orderdetails then orders
less letters :)
Last updated