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
- Break down problems and processes into data
- Create relationships between pieces of data
- Identify and use the correct data type
- Use QuickDBD, Airtable or Google Sheets to prototype a data model
- 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
- Phone
- Additional Guests?
- Can Carpool?
- Paid?
- Relationship?
Objects from a party:
Breaking Down Problems
A Party
Guests
- Name
- 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
Username
Password
How many of each can we have?
One, or Many?
Relational Databases
"Tables"
| ID | Name | 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 | |
|---|---|---|
| 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:
- Break down problems and processes into data
- Create relationships between pieces of data
- Identify and use the correct data type
- Use QuickDBD, Airtable or Google Sheets to prototype a data model
- 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
SQL
There are a billion data types
Keep a cheatsheet handy for your DB
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
- Break down problems and processes into data
- Create relationships between pieces of data
- Identify and use the correct data type
- Use QuickDBD, Airtable or Google Sheets to prototype a data model
- 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
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