Intro to Data Modeling

https://slides.com/lizh/gdi-data-modeling

I'm LizTheDeveloper

17+ years in tech

I've taught over 3500 people to code in person

& nearly a million online

I've been a CTO for hot, VC-funded silicon valley startups twice,

I helped found Hackbright Academy and wrote the curriculum for Galvanize's Web Development Immersive

I wrote the fraud prevention system for 8 of the top 10 banks in the country

and yeah, people still ask me if I'm technical.

Welcome!

Girl Develop It is here to provide affordable and accessible programs to learn software through professional industry mentorship and hands-on instruction.

 

We have a Code of Conduct here

In Summary:

  • You're important and valued
  • Be excellent to each other
  • Help everyone out, it helps you learn!

In This Class

  1. Break down problems and processes into data
  2. Create relationships between pieces of data
  3. Identify and use the correct data type
  4. Use QuickDBD, Airtable or Google Sheets to prototype a data model
  5. Use SQL to define a Schema

You'll revisit these!

Breaking Down Problems

1. State the specific problem or project

2. Determine the abstract categories of objects related to the specific problem or project

3. Determine the characteristics of those objects

4. Determine the relationships between those objects

5. Write 'em down, & talk to another person to see if they make sense!

Breaking Down Problems

A Party

  • Weddings
  • Bar Mitzvahs
  • Fundraisers
  • Graduations
  • House Parties
  • Afterparties
  • Concerts
  • Festivals
  • Launch Parties
  • Meetups...
  • Classes?

What is a party?

Breaking Down Problems

A Party

  • Guests
  • Caterers
  • Volunteers
  • Performers
  • Ticket Sales?

Guests

  • Name
  • Email
  • Phone
  • Additional Guests?
  • Can Carpool?
  • Paid?
  • Relationship?

Objects from a party:

Breaking Down Problems

A Party

Guests

  • Name
  • Email
  • Phone
  • Additional Guests
  • Can Carpool
  • Paid
  • Relationship
  • RSVP'd

Weddings

House Parties

Fundraisers

Concerts

Meetups

Classes

Which Attributes Apply to Which Event Types?

Breaking Down Problems

1. State the specific problem or project

2. Determine the abstract categories of objects related to the specific problem or project

3. Determine the characteristics of those objects

4. Determine the relationships between those objects

5. Write 'em down, & talk to another person to see if they make sense!

Relationship Problems

Let's be clear on the boundaries of our relationships

User

Name

Friend

Spouse

Email

Username

Password

How many of each can we have?

One, or Many?

Relational Databases

"Tables"

ID Name Email Phone Zip
1 Liz liz@liz.com 555-2323 94110
2 Pamela pamela@fox.com 555-3232 94110
3 Ayesugul ayesugul@yonnet.com 555-4455 94110

Relational Databases

Relationships

ID Name Email
1 Liz liz@liz.com
2 Pamela pamela@fox.com
3 Ayesugul ayesugul@yonnet.com
ID User_ID Phone
1 1 555-2323
2 1 555-4612
3 2 555-3232
4 2 555-6187

Relationship Problems

Different Relationships have different characteristics

Users

Following ( a table of relationships)

ID Username Verified
1 lizTheDeveloper false
2 gdi true
3 gdisf false
ID Follower Following Blocked
1 1 2 false
2 3 1 false
3 1 3 false

Relationship Problems

There are lots of different types of relationships

one - one

User : Password

one - many

User : Email

User : Name

User : Bookmarks

Almost everything you think is a 1:1

Relationship Problems

There are lots of different types of relationships

many - many

Friends : Friends

Projects : Collaborators

Jobs : Applicants

Products : Suppliers : Orders : Customers

Tinder Matches : Tinder Matches

Let's Do This

Data Model for Books:

Amazon.com

WorldsWithoutEnd.com

Don't Overthink It

Your guesses about how to structure your data will need to adapt when you start using it

It's ok to change when the volume of data is small

It's still ok to change the model when the volume of data is big, but it is a bigger undertaking

All Together Now

Pick from one of the below problems:

Create a data model by just listing the objects and their attributes

We'll discuss and compare at the end

5-7 Minutes

  • Cookbook
  • Event Poll
  • Team Sports Tournament

(write it down and keep it for the next exercise)

Breaking Down Problems

1. State the specific problem or project

2. Determine the abstract categories of objects related to the specific problem or project

3. Determine the characteristics of those objects

4. Determine the relationships between those objects

5. Write 'em down, & talk to another person to see if they make sense!

We Just Learned:

  1. Break down problems and processes into data
  2. Create relationships between pieces of data
  3. Identify and use the correct data type
  4. Use QuickDBD, Airtable or Google Sheets to prototype a data model
  5. Use SQL to define a Schema

You'll revisit these!

Data Types

Text and Numbers aren't the same

7 + 6 = 13

"7" + "6" = "76"

Data Types

Dates aren't Numbers

3/25/19 15:45:00:00 + 1 = ????

1 what? 1 year? 1 day? 1 millisecond?

Computers Don't Know (Or Care) What Your Data Is

Tell Them

AirTable

QuickDBD

SQL

There are a billion data types

Keep a cheatsheet handy for your DB

PostgreSQL Cheatsheet

MySQL Cheatsheet

Airtable Field Types

Time to throw down

Create your data model from before in Airtable

5-7 Minutes

Let's Get Texty

After you've agreed this is a good data model, let's throw it in QuickDBD

2-5 Minutes

app.quickdatabasediagrams.com

Choose Datatypes from Postgres or MySQL

At the end, hit Export!

We Just Learned

  1. Break down problems and processes into data
  2. Create relationships between pieces of data
  3. Identify and use the correct data type
  4. Use QuickDBD, Airtable or Google Sheets to prototype a data model
  5. Use SQL to define a Schema

You'll revisit these!

Together

Let's Analyze the Schema you produced & Discuss

SQL Tables

CREATE TABLE pokemon (
    id SERIAL PRIMARY KEY,
    name TEXT,
    pokedex_number VARCHAR(10),
    type1 TEXT,
    type2 TEXT
);

Defining A Table

Infrastructure of Types

  • Serial
  • Integer
  • Numeric
  • Text, Varchar
  • JSON
  • BLOB
  • Timestamp
  • Boolean (True or False)

Homework & Further Reading

A caution not to over-normalize everything

Airtable Power User's Guide: Practice Data Literacy

quickdatabasediagrams.com

https://sqlzoo.net/ - Query Datasets with SQL

For Feedback:

Happy to give feedback on any data models you might have that you feel are too complex for you to solve for yourself.

 

Try mocking them up first in quickDBD or AirTable and using them

 

Next, email me at lizthedeveloper@gmail.com