Intro to Schema Design
A representation of a plan or theory in the form of an outline or model.
1. Design Process
Designing your schemas is perhaps even more important than writing SQL!
Steps to a Schema Design:
- Conceptual Design
- Logical Design
- Physical Design
ERD – Entity Relationship Diagram
(A graphic of) your entities and the relationships between those entities.
Databases should be DRY!
We may make additional tables to make a database DRY. However, making additional tables can slow down our DB.
Normalization is making your data less redundant by, for example, splitting your database into separate tables.
Normalized databases are split up into different tables to reduce redundancy. These work best when app is write-intensive and write-load is more than read-load.
If you're doing lots of writing, normalize your DB!
Normalized DBs need join tables through, which means read operations on multiple tables suffer.
We can also: denormalize our data.
Works best when application is read-intensive. A single table with all required data allows for faster reads.
Three Main relationships
- has many
- belongs to
- has many through <== here's the tough one
Example: Dog Shelter
- The Dog
- Name and Breed
- Human Adopter
- Suitable Home
- Address of Adopter
What do I need in my data?
Set up your entities and look at their relationships. For example, what is relationship between HUMAN and DOG?
A human can adopt many dogs. Our database must show that.
But a dog can only have one owner.
Once we map the relationships, we add attributes to each of our entities.
Now we have our ERD!
Look at slides for visuals of our Dog Shelter ERD
Specifically, what are my tables? What are my attribute names?
Here's what our dog entity would look like.
Dogs ________________________ id | int human_id | int name | string breed | string favorite_toy | string
Example: Journal App
I want to be able to enter text. I want to be able to see entries chronologically.
Journal Entry with DATE and BODY attributes
entries _______________________________ id | int primary key date | date entry | string
What if I want to #tag entries? Or have multiple journals?
Do process over again!
In this more complex structure with tags, entries, and journals -- we'll need to use foreign keys.
Many tags could belong to the same entry. And an entry could have many tags.
For this we'll need a join table, which enables many-to-many relationships. A join table is really just a table of relationships.
For example, our join table, called entries_tags, could tie an entry_id to a tag_id. For every tag on an entry, we have an entry id and a tag id.
See slides for an ERD of entries_tags