Database Relationships – Many to Many / One to One

by Trevor Page on January 7, 2014

Play

Java-Podcast
In a previous post we learned a whole bunch about the most common database relationship that exists, the one-to-many relationship.

In this post we will be expanding on the topic of database relationships and touch on two that are less common but just as useful.

Many-to-Many Relationship

The many-to-many database relationship is used when you are in the situation where the rows in the first table can map to multiple rows in the second table… and those rows in the second table can also map to multiple (different) rows in the first table.

If that quick and dirty explanation is a bit too general, let’s take a look at a real world example!

Let’s think of books and authors and decide what that relationship looks like.

The first question we ask is: Is there indeed a need for a “Many” side of the relationship?

Which means: can an author create “many” books? The answer is definitely Yes!

The second question we ask is: Does the table (Object) on the “Many” side actually only map to 1 item in it’s related table?

Which means: can a book only be written by one author? The answer here is No many books have been published by multiple authors!

So this means that we’re definitely in the many-to-many arena with this relationship.

460px-CPT-Databases-ManytoMany.svg

figure 1

How do you create a Many-to-Many relationship in SQL?

This is where things get slightly different from the more popular One-to-Many relationship.

A good design for a Many-to-Many relationship makes use of something called a join table. The term join table is just a fancy way of describing a third SQL table that only holds primary keys. You see, it’s easy to draw out this relationship on paper, you can see an example of it in figure 1 above. When it comes to creating this relationship in terms of SQL tables, it’s just one step more complicated.

First let’s outline what the author and book tables could look like in SQL.

Author
<<PK>> author_id [int (11)]
first_name [varchar(20)]
last_name [varchar(20)]
Book
<<PK>> book_id [int (11)]
book_title [varchar(255)]
ISBN [varchar(255)]
version [varchar(10)]

Okay, so this is how the book and author tables could look like, but there’s no relationship defined yet! So let’s create one… since this is a Many-to-Many relationship and because I already mentioned that you’ll need to use a join table when implementing a Many-to-Many relationship, let’s see what this join table should look like.

Author_Book
<<FK>> author_id [int (11)]
<<FK>> book_id [int (11)]



A few things to note here:

  1. By convention, the name of this join table is usually just the combination of the two tables of the many-to-many relationship. In this case it’s just author_book, which implies that this is a join table since it’s using the name of two existing tables joined by an underscore.
  2. This join table only contains the primary keys from the author and book tables. Since this join table is referring to primary keys that don’t actually belong to the join table, they are actually referred to as foreign keys.
  3. Sometimes it’s useful to assign a primary key column to a join table ( i.e. author_book_id [int (11)] )

So now that we’ve created this join table, we will be able to easily create ANY relationship by inserting the appropriate rows into the join table. For example, if author “Trevor Page (author_id=14232)” created the book “How to Program with Java (book_id=9127329298)” then you could just insert the following row sinto the join table:

  insert into author_book (author_id, book_id) values (14232, 9127329298);
  insert into author_book (author_id, book_id) values (14232, 9127329298);

So this will create a relationship between “Trevor Page” and “How to Program with Java”, but let’s say Trevor Page publishes another book (book_id=9315619872) and has some help from another author (author_id=14585) who also happens to have authored another book (book_id=8181225133), we can just insert those values into the join table to create that many-to-many relationship:

  insert into author_book (author_id, book_id) values (14232, 9315619872);
  insert into author_book (author_id, book_id) values (14585, 9315619872);
  insert into author_book (author_id, book_id) values (14585, 8181225133);

So now we have author “Trevor Page” who owns two books. One of those books has a second author, and that second author also owns a book that “Trevor Page” does not.

Piece of cake right?

Not really I suppose, I had a lot of trouble figuring out the intricacies of the many-to-many relationship at first. So don’t worry if you don’t fully follow it, it’ll come with time and practice!

One-to-One Relationship

Okay, so let’s switch gears to the easiest relationship to understand. That’s the One-to-One relationship. This one should hopefully be self-explanatory at this point, but if it isn’t, I shall explain.

A One-to-One relationship means that you have two tables that have a relationship, but that relationship only exists in such a way that any given row from Table A can have at most one matching row in Table B.

A real world example of this could be the relationship between a person and a drivers license. Can one person have more than one drivers license? In the case of North America, the answer is no, any given person cannot have more than one drivers license. Well then, what’s the reverse case? Can one particular drivers license be owned by more than one person? Again, in North America’s case, the answer to that is no as well. One drivers license is assigned to one person, and ONLY one person.

So this means we can a One-to-One relationship. If I were to pick out ANY drivers license from a huge pile of drivers licenses, any individual license would point me back to ONE person in particular.

How do you create a One-to-One relationship in SQL?

The trick to creating a one-to-one relationship in SQL is to identify which table is on the “right hand side” or “child” of the relationship. This is usually done by deciding which object can exist without the other.

So ask yourself this question: Can a person exist without a drivers license? The answer is yes (I would hope)… then, can a drivers license exist without a person? I would say no, you cannot create a drivers license that doesn’t belong to someone, it just wouldn’t make sense.

So this is much like a parent/child relationship right? The parent in this case is the Person, and the child is the drivers license. You’ll find that with the One-to-One relationship, this will be the case most of the time.

Since we’ve established that the drivers license is the “child” of this particular one-to-one relationship, we can move forward with our table design.

When designing the SQL tables for the one-to-one relationship, you’ll need to make sure that the “child” table’s primary key, is also the foreign key of the “parent” table. So this means that the drivers license table’s primary key, should actually be the person table’s key. It will look something like this:

Person
<<PK>> person_id [int (11)]
first_name [varchar(20)]
last_name [varchar(20)]
Drivers_License
<<PK/FK>> person_id [int (11)]
license_number [varchar(20)]
issue_date [datetime]
expiry_date [datetime]

So the important thing to note here is that the drivers_license table does NOT have it’s own drivers_license_id column, as that would break the design for a true one-to-one relationship.

Please help to support these podcasts by filling out a quick survey. This will help keep this information FREE for you to consume and it will help me to keep creating these valuable tutorials at a predictable pace!

Keep Podcast Free

{ 1 comment }

Database Relationships – One to Many

by Trevor Page on December 12, 2013

Play

We’ve talked about relational databases already, and we’ve learned why this type of database management really dovetails with the object oriented programming model. So now I want to dive into the specifics when it comes to relationships.

What are the different types of relationships in SQL?

There are three types of relationships you can have in SQL, they are:

  1. One-to-Many
  2. One-to-One
  3. Many-to-Many

In this episode we are going to be focusing on the One-to-Many relationship as it’s the most commonly used in my opinion. Let’s start off our talk by first exploring what a One-to-Many relationship looks like.

What does a One-to-Many relationship look like?

The typical example of a one to many relationship is when you’re talking about Users and Addresses. Typically a User can have one or more addresses (perhaps a mailing address and a billing address). The key here is that (in this particular design) any ONE Address can only belong to ONE User and ONLY ONE User.

This means that, for any particular Address that you could pick from the database table, that Address will only belong (or map to) exactly one User. This is what makes the relationship a One-to-Many relationship.

Other real world examples could include:

  • 1 Employer has many Employees
  • 1 Guitar has many Guitar Strings
  • 1 Car has many Seats

There are countless different real world examples that can be thought up for this One-to-Many relationship, the key thing to understand is when to choose this relationship in your program.

When to choose a One-to-Many relationship

You’ll need to essentially ask yourself two questions to decide if you indeed want to implement a One-to-Many relationship:

  1. Is there indeed a need for a “Many” side of the relationship? For example, what if your system only needs a User to have ONE Address? This would negate the use for a One-to-Many relationship, as any given User is required to input exactly one Address
  2. Does the table (Object) on the “Many” side actually only map to 1 item in it’s related table? For example, what if you wanted to have any one particular address belong to multiple Users? Perhaps your system needs to keep track of which of its Users live at the SAME Address (like brother and sister, roommates or spouses). If this is the case, then you’d probably want to choose a Many-to-Many relationship

As you can see, there are many ways that you can implement a simple User -> Address relationship. You could make it a One-to-One, One-to-Many or Many-to-Many… it’s all in the design of your application and how YOU want it to function.

These are the kinds of things you’ll need to consider before committing to any particular table relationship.

How do you create a One-to-Many relationship in SQL?

Creating this One-to-Many relationship is all about your primary key <<PK>>. Let’s say we have chosen to use the One-to-Many relationship for our User -> Address mapping.

We know that we need to have a primary key <<PK>> for both tables. So our database tables may look something like this:

Users
<<PK>> user_id [int (11)]
username [varchar(20)]
password [varchar(20)]
Address
<<PK>> address_id [int (11)]
street_address_1 [varchar(255)]
street_address_2 [varchar(255)]
region [varchar(50)]
zip_code [varchar(7)]
country [varchar(50)]

Important Note: this hasn’t yet defined our One-to-Many relationship! We still need to make use of something called our Foreign Key <<FK>>.

To add our relationship to these tables, we’ll need to add in a foreign key <<FK>>. This foreign key is what is used to create a “link” between our tables. The typical way this is done, is to insert your foreign key into the table that represents the “Many” side of the One-to-Many relationship.

In this case, the “Many” side is the Address table. So we’ll need to add a link to our User table into the Address table. This can be done by inserting the primary key of the User table into the Address table.

Since we know that the primary key (by definition) will always only point to ONE unique row of data, this will be perfect for keeping track of which Address row is related to which User row.

Let’s put our User table’s primary key into the Address table:

Users
<<PK>> user_id [int (11)]
username [varchar(20)]
password [varchar(20)]
Address
<<PK>> address_id [int (11)]
<<FK>> user_id [int (11)]
street_address_1 [varchar(255)]
street_address_2 [varchar(255)]
region [varchar(50)]
zip_code [varchar(7)]
country [varchar(50)]
There we have it! We now have a design for our database tables that incorporates the One-to-Many relationship using a foreign key!
Please help to support these podcasts by filling out a quick survey. This will help keep this information FREE for you to consume and it will help me to keep creating these valuable tutorials at a predictable pace!

Keep Podcast Free

{ 1 comment }

Database Joins, Keys and Relationships

by Trevor Page on December 4, 2013

Play

cc_square

Coders’ Campus

Before we get into the show notes for this podcast episode, I want to spend some time talking about a brand new community.

I’m a BIG believer in online communities. Masterminds and communities are the main reason why I’ve come as far as I have in my life. Online communities are where I’ve met some of my closest friends and they have guided me through some very challenging parts of my career.

So it came as a bit of a shock to me that I don’t belong to an online community dedicated to programmers.

When I tried to find a community for programmers online, I just couldn’t find any good ones.

So, being the “go-getter” that I am, I’ve decided to start our very own community dedicated to programmers and the advancement of our common goal – To be a success as a programmer. I’ve named this community Coders’ Campus and it will be dedicated to helping programmers succeed in their programming journey.

Benefits of Joining Coders’ Campus

  • Help with assignments and exams from your community through HipChat, Google hangouts and email
  • Video walkthroughs of homework assignments from experts
  • Access to a constantly updated list of interview questions and answers
  • Recurring Google hangouts where you can meet and greet your new friends in the community, network and share your knowledge
  • Mock interviews to ensure you NAIL your next programming interview
  • Help with solving those annoying bugs in your program and more!

Join Coders’ Campus



Database Keys



Okay, now that I’ve talked a bit about that cool new community for programmers, it’s time to go over the show notes for this particular podcast episode.

Here’s a diagram that outlines the example relationship I was mentioning in the podcast episode. This one uses “Account” and “Address”, but you can just replace “Account” with “User” to make it work with the example I mentioned in the episode.

Account - Address Relationship

Here you’ll notice that the “Account” table has declared a primary key known as “Account_id”, and the “Address” table holds a relationship via the foreign key “Account_id”

This relationship is what we use to be able to JOIN data from the “Account” table to the “Address” table.

Here’s an example of how we would read data from these tables by joining them together:

select * from Account
inner join Address on Account.account_id = Address.account_id;

This will actually join ALL the data from the “Account” table to the “Address” table… but what if we just want to get the information for a single account? Well we would just add a ‘where’ clause to our sql like so:

select * from Account
inner join Address on Account.account_id = Address.account_id
where Account.firstname = 'trevor'
and Account.lastname = 'page';

{ 1 comment }

Intro to SQL and Databases

by Trevor Page on November 28, 2013

Play

Java-Podcast

What’s all this CRUD about?

  • Create
  • Read
  • Update
  • Delete

This is the at the heart of all databases and SQL. A database essentially carries out these four operations over and over again for the duration of its existence.

In this podcast (which you can play above), I talk about these four database operations in detail and the actual syntax that is used in a flavour of SQL known as MySQL.

MySQL

You can think of SQL as kind of a specification that needs implementation. In Java terms you can think of SQL like an interface and MySQL like the class that implements it.

I’m choose to go into depth with MySQL as it’s a free implementation of SQL and it’s popular and widely adopted with plenty of HOW-TO articles around the net.

If you wish to learn how to install the MySQL RDBMS and a GUI for manipulation your database, then please check out this video:

Create

Let’s talk syntax!

The first step in our CRUD operations is the Create operation. This is used to create data in the database. This can be accomplished using the insert keyword.

Let’s assume you would like to insert a new User into your database. How would you accomplish this?

I like to approach these questions with the mindset of “What would I need to know if I was the database management system?” I would need to know where to insert the data, and specifically what data to insert.

Let’s see what that looks like in the MySQL syntax:

insert into users (username, password) values ('tpage', 'password123');

In the SQL code above, we have satisfied the questions of where and what. The where is satisfied by specifying the name of the table in which the data is to be inserted, and also the columns we wish to populate. The what is satisfied by the actual data we are providing in the single quotes. For more info on this, be sure to actually listen to this podcast by clicking the play button at the top of this post.

Read

This is by far the most commonly used operation in any database. The ability to retrieve/read data in a database is crucial to a properly functioning application. In order to read information from a database using MySQL you’ll use the select keyword. I’m not sure why they didn’t choose to go with a keyword like “read”, but hey, what can you do!

Just like in the “Create” section, when “Reading” information from a database, you’ll need to think of what the database management system would need to use to get you to the information that you want. In this case, it’s mostly just a matter of “where” is the data that you want. Let’s assume you want to retrieve the username and password for a particular user to validate that they are indeed a valid user. What would you need to specify? Well, you need to tell the database where this information exists in terms of the table and the columns. Here’s an example:

select username, password from users;

Now this code will give you the username and password of ALL the records in the users table. This isn’t exactly what we wanted, we specifically want to verify if ONE particular user exists in the database and what that particular user’s username and password is. So how do we accomplish this with SQL? It’s all about the “where”!

select username, password from users where username = 'tpage';

Don’t forget that you can also keep ‘chaining’ the where question in MySQL using the and keyword like so:

select username, password from users 
where username = 'tpage' 
and password = 'password123';

This will only return a match if you have a user that exists in the users table with the username = 'tpage' which has a password = 'password123'.

Note: You can see that I broke up the above SQL statement with carriage returns so that it becomes a bit more readable. I separate each ‘section’ of the syntax with a line break.

Update

When you want to change the data for any existing row in a database, you need to invoke an Update using the update keyword.

Let’s say that you wish to change a given user’s email address… how would you do it?

update users 
set email = 'trevor@javavideotutorials.net' 
where email = 'info@howtoprogramwithjava.com';

This is kind of tricky, but it functions just like a Java statement. The database management system will essentially read this statement in chunks.

  1. It will figure out what table will be changing (this is found in the first line of the script above)
  2. It will figure out which row(s) that need to be updated
  3. It will change the data to the value you’ve specified

In other words, the database management system (MySQL) is able to find the row of data that needs to be updated with the last line (where email = 'info@howtoprogramwithjava.com'), it will then take the row(s) that it finds and execute the update that you’ve specified to change the row(s) email to be ‘trevor@javavideotutorials.net’.

Note: It’s very important that you know the results of the update statement before it’s run, as you don’t want to accidentally update MORE rows than you intended. For this, I would recommend copy/pasting your update statement and changing it to reflect a select statement like so:

--update users 
--set email = 'trevor@javavideotutorials.net' 
--where email = 'info@howtoprogramwithjava.com';

select * from users 
where email = 'info@howtoprogramwithjava.com';

This way you can run the select statement and ensure that it returns the expected rows. Once you’re satisfied with the rows that are returned, you can run the update statement and it will then update those rows that you saw in the select statement.

Make sense?

Delete

The final main operation that MySQL can carry out is a delete. This is used to complete destroy a row (or multiple rows) of data. This is very similar to writing a select statement, in that you are narrowing in on the data that you wish to delete. Let’s say we want to delete the row of data that pertains to the user with the email address ‘info@howtoprogramwithjava.com’. It would look something like this:

delete from users
where email = 'info@howtoprogramwithjava.com';

Easy peasy. The only thing that trips me up sometimes with the delete statement, is that sometimes I put an asterisk next to the delete keyword. It feels natural to want to say “delete ALL from users” by typing delete * from users, but this will in fact result in a syntax error.

Podcast Survey

I hope I have helped to shed some light on the mysteries of SQL!

Please help to support these podcasts by filling out a quick survey. This will help keep this information FREE for you to consume and it will help me to keep creating these valuable tutorials at a predictable pace!

Keep Podcast Free

{ 1 comment }

Publish Java Web Application via VPS

by Trevor Page on November 1, 2013

publish

So you’ve created a Web app… Now what?

This is a question that haunted me for a very long time. I have the know how to create an entire web application, but I didn’t have a clue how to actually publish this web app so my friends, family and co-workers could see it.

In the end I spent countless hours pouring through blogs, videos and asking questions online and off. It was a painful process and I was met with countless barriers.

So I put together a video that takes you through the ENTIRE process, step by step.

I’m very proud of this tutorial, as I’m not aware of any other like it on the web (at least none that I could easily find or that answered all of my questions about publishing a Java web application).

What You’ll Learn from this Video

  1. Why would I want to publish my Java web application?
  2. What resources/technologies are involved in the publishing process?
  3. How much does it cost to host/publish a Java web application?
  4. How long does this process take?


Why would I want to publish my Java web application?

There are two main reasons why you’d want to publish your work to the internet for all to see.

The first reason is that it’s nice to have your work published so that you can have other people use your app and test it out. Most of the bugs that are found during the development life-cycle of an application come from the QA process (quality assurance). So having your work publicly available allows for a much broader audience to access your application and test it for you.

The second main reason is… Why wouldn’t you want to publish your cool application? The whole point of slaving away in front of a computer and programming all day is so that you can unleash your work on the world and receive fame and fortune right?! In all seriousness, publishing your web app is usually the end goal of any development process, so it’s a good idea to become familiar with the process.

What resources/technologies are involved in the publishing process?

The main resource you’ll need is something called a virtual private server (VPS). Java applications require web servers that aren’t usually available with regular hosting plans, so the next step up from a regular run of the mill hosting plan is to get yourself your own VPS.

Essentially a VPS is your very own computer that you’ll be able to “virtually” log into and use. A virtual private server is often just a “server image” that exists along side MANY images on a single physical server… Picture you taking your computer and slicing it into many smaller less powerful computers and then selling people access to those less powerful computers. That’s what a VPS is in a nutshell.

With your own VPS, you can use it just like it’s a regular computer, so you can install all of the software you’ll need to host your Java web application.

Note: There’s also hosting companies that provide dedicated servers, which is an entire machine dedicated just to your purposes (so you’re not sharing a single computer’s resources with many other people). This extra power comes at an additional cost of course.

How much does it cost to host/publish a Java web application?

Thankfully all of the software you can use to host your Java web applications is free, but the actual VPS is not. VPS plans vary in price, but the key is the find the best bang for your buck. I’ve tested around 6 different hosting companies and I’ve found that the cheapest VPS you can get that still packs reliability and speed is via InterServer. You can get plans for around $8-$11 per month, which is fantastic for a VPS!

How long does this process take?

The whole process takes an hour or two, so in the grand scheme of the life-cycle of the development of your software, it’s a drop in the bucket! I’ve put together a 45 minute video that takes you step-by-step through this process and explains everything you need to know in detail.

My goal here is to have covered all of your bases for being able to publish your web application online so that you can send a URL over to your friends and family bragging about your programming abilities ;)

Please enjoy the video and if you have any questions or run into any issues, please leave a comment below the video and I’ll be happy to help you out.

{ 2 comments }

Algorithms and Big O Notation

October 30, 2013

Job Interview Dilemma This is essentially the question that I received from a fellow podcast listener. This person encountered this question during a job interview where they were asked how they would determine which of 5 balls were the heaviest if they were only given a scale that only tells you relative weight. Once this [...]

Read the full article →

Hibernate Eager vs Lazy Fetch Type

October 25, 2013

Background The topic of Fetch types in Hibernate is a fairly advanced topic, as it requires you to have a decent understanding of the Java programming language, as well as have had exposure to SQL and the Hibernate framework for Java. In this post I’m going to assume that you posses this knowledge, if you [...]

Read the full article →

Java Podcast – Spring Security

October 23, 2013

Background Spring Security was created to solve a very common problem for web application developers: “How can I make sure that ONLY people who have paid/signed-up for my web application have access to my web application?” This is what Spring Security is all about, it provides an easy way for you to “secure” the access [...]

Read the full article →

Podcast Episode 35 – Spring InitBinder Method

October 16, 2013

Podcast Survey First thing’s first, I need to ask you for help. Can you help keep these podcasts and blog posts free by filling out a quick survey? It’ll take 10 seconds, I promise! Podcast survey Data Binding So, the Spring framework is a wonderful help for programmers, but sometimes it needs a bit of [...]

Read the full article →

Podcast Episode 34 – Fun with Controllers

October 9, 2013

Before we get started… I need your help. It’s been about two months since my last podcast was released and I’ve decided that since this has become a recurring thing, it’s time I did something about it. To help me keep these podcasts and blog posts FREE, I need you to take 10 seconds to [...]

Read the full article →