Let’s build a FullStack App with JavaScript! ~ Episode 3

This is a multipart series about building a Puppy Adoption Site with JavaScript. If you haven’t read all the articles please find them here: Episode 1| Episode 2 | Episode 3 | Episode 4 | Episode 5-Part 1 | Episode 5-Part 2 | Episode 6

Let’s Model our Data!

Before we can build our database it’s important that we take some time to design what our database will look like. This might sound tedious but when we start thinking about data, things can get complicated quickly.

For example, in our application alone we’ll be juggling admins, kennels, dogs, and more. Planning out how these will all fit and relate in our database will go along way towards preventing problems in the future.

Let’s review some of the basics regarding database structure and then map out how our database will look!

What’s behind a Database?

We understand that a database is going to store a lot of information for us. As I mentioned above, our database will store lots of unique information and eventually they’ll need to communicate with each other. This is awesome, but how does a database do that?

The answer: tables. A table is where we organized our data into specified rows and columns. Usually, we define tables by the unique data we’re planning on storing in our app. Ultimately, this makes our database a collection of tables.

Ok, we can comprehend a group of tables storing data, but how do we decide what becomes a table?

A fun trick I learned is to think of tables in terms of the nouns you use in your application. For example, our app will allow admins to log in and edit a kennel’s or dog’s information. From this sentence, we can narrow pick out the nouns: admins, kennels, and dogs and start framing them as potential tables for our database.

Do tables talk?

Now that we’ve started to understand data tables, we also need to start thinking about how these tables interact? For our project, we’ll be relying on three items to help us create connections between tables. Those being, primary keys, foreign keys, and relational tables.

Primary Keys & Foreign Keys

When we store a piece of data in one of our tables we need to assign it a primary key. This primary key is important because it will be our target for when we want to retrieve that specified piece of data.

Often, we’ll set the primary key to be an id. The database we plan on using allows us to add an autoincrement property to an id value so when we submit our data it autogenerates a unique numeric id relative to the values already stored on the table.

When we define primary keys, we can then use them to form relationships with other pieces of data from other tables. Three types of relationships we can create are One to One, One to Many or a Many to Many.

To create one One to One or One to Many relationships we utilize an objects primary key as a foreign key. We implement this by assigning a piece of data’s primary key to another object as a foreign key. This creates a connection between both data points.

An example of a One to One relationship in our App would be a Kennel’s primary key being used as a foreign key with one of our Admins. This ensures that each Kennel has only one Admin and each Admin only has one Kennel.

An example of a One to Many would be a Kennel’s primary key being used as a foreign key with multiple dogs. That way a Kennel can have multiple dogs linked to it but this prevents a single dog being assigned to multiple kennels at one time.

At the end of the day, we need primary keys so that the implementation of foreign keys becomes seamless.

Relational Tables

Another relationship will need to consider is the Many to Many relationship. This allows two tables to share many data points. It’s similar to a One to Many in that items are being assigned multiple connections, however, they’re not limited to one.

In our kennels and dog connection, we’re assigning multiple dogs to a single kennel. However, if we let dogs be assigned to multiple kennels then we’d be creating a many to many relationships. Both sides of the connection can have multiple relations.

While this sounds useful, there’s no way for us to do that with exclusively foreign keys. In order to combat that we construct a third table, sometimes referred to as a connection table or relational table.

The third table allows us to explicitly define connections between the tables in a Many to Many relationship. This will make more sense when we go through seeding but for the moment just understand this table is completing the handshake in our Many to Many. If you’re craving more info on Many to Many relationships, I highly recommend this video.

Let’s Talk about Doggy Data

Now that we can begin to visualize our database as a bunch of tables communicating with each other, let’s think about how we want each table to be structured and then they all should interact.

For starters, let’s make a list of what we want our app to do.

1. We want visitors to be able to find dogs for adoption.
2. Dogs can be filtered by Kennel and Breeds
3. Visitors can check dog availability at specific Kennels.
4. Kennels will have profile pages about themselves.
5. Dogs will have profile pages about themselves.
6. Admins (kennel owners) can log in and create, delete, or update their dog inventory.
7. Site visitors can send Admins notifications when they want to adopt a dog.

That’s a lot of actions! But let’s look at the nouns to decide what tables are necessary for our database. The tables I defined were:

Tables

• Kennels
• Dogs
• Breeds
• Dog_Breeds (Many to Many Table for Dogs & Dog Breeds)
• Admins
• Notifications

This is great! We now know what tables are needed for our project. We’ll next need to define the data we want each table to carry but before we do let’s think briefly about which tables will be communicating together. Here’s a list of the connections we’ll need:

Table Connections

• Kennel to Admin (One to One)
• Kennel to Dogs(One to Many)
• Dogs to Breeds(Many to Many)
• Admin to Notifications (One to Many)

Not too crazy, we can definitely plan for those connections! This is great to define before we plan what each table will look like so we know what foreign keys to include. I’ll dive into the specifics of the relationships a bit later.

Table Data

To create a robust application, we want to ensure that we have a lot of data to inform users about our dogs and kennels. In order to do that we’re going to need to store that information in our tables. We can define the columns of our tables so that when a user submits data it will need to correlate to them

Let’s map out what we’ll think each table’s columns should look like in order to keep our app up to speed:

Data Submission Outlines:

Kennels

• Unique ID (Primary Key)
• Location
• Bio
• Email
• Phone
• Image

Dogs

• Unique ID (Primary Key)
• Kennel_ID (Foreign Key)
• Name
• Age
• Size
• Bio
• Sex
• Price
• Image

Breeds

• Unique ID (Primary Key)
• Name

Dog_Breeds

• Unique ID (Primary Key)
• Dog_ID (Foreign Key)
• Breed_ID (Foreign Key)

Admins

• Unique ID (Primary Key)
• Kennel_ID (Foreign Key)
• UserName
• Password

Notifications

• Unique ID (Primary Key)
• Admin_ID (Foreign Key)
• Email
• Message

Awesome! By listing, we now have a great idea about what each entity will need when they’re submitted to a table.

Now that we’ve defined each table, what traits a data entry needs to be submitted and the connection between each table. let’s visualize these connections.

I usually draw this out on a piece of paper but for this project, I looked to Illustrator so it’d be nice and neat for y’all 😉

Ain’t that beautiful! Each square representing a table with their columns/required data inside, and the relationships are color coordinated too!

As promised above let’s dive into the connections we’ve created:

Kennels to Dogs (One to Many):

We set up a One to Many relationship so that our kennels can store many dogs within it. However, we do not want a dog to exist in multiple kennels. Therefore, a One to Many is a better choice than a Many to Many.

Kennels to Admins (One to One):

Similar to our Kennels to Dogs connection, except we only want one Kennel assigned to one Admin and one Admin assign to one Kennel. At this point, we don’t want to encourage Kennels to have multiple Admin accounts and we’d like for each Kennel to be represented by solo committed Admin as well. Therefore a One to One is a perfect choice.

Admins to Notifications (One to Many):

We want our Notifications to correspond to a single Admin. However, we need our admins to receive multiple notifications all at once. Utilizing a One to Many relationship allows us to connect the two.

Dogs to Dog_Breeds to Breeds (Many to Many):

Lastly, the one that might appear to be the easiest ends up to be the most complicated. That’s because while a single dog can have many types of breeds a breed can also have many dogs associated with it. (Notice the usage of many in the previous sentence?) Because of this relationship using a Many to Many relationship and a connection table to join our Dogs and Breeds will allow us to dynamically assign multiple breeds to any dog in our database.

Wrap Up

For a codeless article, there was certainly a lot to consider. I hope this provided you a good framework to start thinking about data modeling and how we want to visual our data before diving into database construction.

If you’re feeling a little confused, don’t worry! Feel free to reach out or wait patiently because the next episode, where we build our database should provide a lot of contexts.