Intro to Schema Design – FSA 1702 – March 8, 2017

Intro-To-Schema-Design.md

Intro to Schema Design

A representation of a plan or theory in the form of an outline or model.

1. Design Process

2. Examples

---

1. Design

Designing your schemas is perhaps even more important than writing SQL!

Steps to a Schema Design:

• Analysis
• Conceptual Design
• Logical Design
• Physical Design

ERD – Entity Relationship Diagram

(A graphic of) your entities and the relationships between those entities.

Data Normalization

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

2. Examples

Example: Dog Shelter

We need:

• The Dog
• Name and Breed
• Human Adopter
• Suitable Home
• Address of Adopter

Analysis

What do I need in my data?

Conceptual Design

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

Logical Design

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

Analysis

I want to be able to enter text. I want to be able to see entries chronologically.

Conceptual Design

Journal Entry with DATE and BODY attributes

Logical Design

            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

---