Hi, my name’s Taylor and I’m going to
walk you through some advanced ERD concepts. Today we're going to be going over primary keys, foreign Keys, bridge tables, and then really understanding how ER diagrams help you see exactly what’s going on in your database. So let’s start where we left off---
with our very simple diagram from the previous tutorial. So we’ve got entities for Customer, Order,
and Product and the correct cardinality between. You’ll notice that next to each attribute
is the word “key.” This column is here so we can distinguish
certain attributes as either primary keys or foreign keys. Let’s start with primary keys. This is probably easiest to understand if we step out of the diagram and into the table it represents. Remember, each entity in your diagram represents
a table within your database. So let’s look at the Customer entity as
a table with rows and columns. We’ve got all these different instances
of the customer here. And if you’re Amazon or something, you’ve
got millions of customers all within this table. Wouldn’t it be nice if you could use a single
trigger to quickly and accurately distinguish who’s who in this massive list? That’s where a primary key come in. A primary key is an attribute (or field) that
uniquely identifies every record in a certain table. And since a single attribute can accomplish
all that, it makes sense that you’ll need just one primary key per entity. So for this customer table, the primary key
is going to be a value that makes it so that this customer is unlike any other customer
in our table. For one of these attributes to qualify, there
are a few rules. First, a primary key has to be unique, so
that it identifies only one record in your table. Second, it needs to be never-changing. It’d be pretty difficult to keep an accurate
record of your customers if you’re using an attribute that might not stay consistent. And finally, a primary key needs to be never-null. That just means there wouldn’t be an occasion
where the value could be left blank. So let’s look at a specific customer, like
John here, and determine what data might uniquely identify him. Well, you can’t rely on names, because two
totally different customers could very easily share the same first and last name. There’s a John Smith here and a John Smith
here...but they’re two completely different customers. So it's not a unique record in this table. You can’t even rely on an address because
you could have two separate customers living at the same place. Or a customer could move and then their address
changes. So an address isn’t unique and breaks the
rule of “never-changing.” Same thing for a phone number. So that leaves us with customer ID. By design, any sort of ID is typically programmed
to increment for each addition to the table. This customer signs up and is assigned Customer
ID 30016. Then this customer signs up
and is assigned 30017, and so on. You can see how Customer ID passes all our rules. John’s Customer ID will completely identify
him as a particular instance in our database, and that value won’t ever be repeated in
this table. So we’re going to make that our primary key. Here’s something interesting to think about
while we’re on this topic. Have you ever tried to create a username for an account,
and then later wanted to change your username to something else because it was hard to remember
or stupid or something... but then you can’t because the site won’t let you? Well that’s probably because your username
is being used as a primary key in that site’s database. And primary keys can never change. That’s how the site is linking you, the
customer, to your account. They don’t let you change that primary key
because their system relies on it for accurate records. Or how about when you’re setting up a new
account and you try to create a username but get an error message saying that it’s already taken. Again, this could be happening because the
username is being used as the primary key, and a primary key can’t be repeated. In our example though, we’re just using
a randomly assigned Customer ID number as our primary key. We know this will always be unique and never
repeated. So let’s jump back to our diagram and note
this by putting PK next to our Customer ID. And I’ll go ahead and quickly clear out
all this other text to make things clearer. Now let’s apply those same rules to find
primary keys for our other entities. Let’s quickly jump into the Order table
that this Order entity represents, and zero in on a specific instance. Here it looks like our customer, John, placed
an order to go to this address and it shipped at this time. Looking at the data, what could be our primary
key? We already know names and addresses are no
good, and ship date isn’t going to work either. Two orders could be shipped at the exact same
time, so that’s not a unique attribute. It’s pretty clear that Order Number is the
primary key. So we put PK next to that attribute in our
diagram. And finally we can look at the table that
this Product entity represents. Let’s say our customer, John, purchased
this product here. A beautiful snuggie. You know by now that Product ID is going to
be the primary key in this table. So we note that in our diagram, too. Each of these primary keys is unique, never
changing, and never-null. So you probably noticed that I’m using a diagramming
software to modify these ERDs, but just know that the same concepts apply if you’re drawing them out on pen and paper. But if you want to make things easier on
yourself, you can sign up for the diagramming software that I’m using. For free actually. It’s called Lucidchart. All you have
to do is pause the video, click on the card in the top-right corner, and sign up for
an account. All it takes is an email address and you can
follow along with me. So now that we have our primary keys
nailed down, let’s talk about foreign keys. A foreign key is the same as a primary key, but just located in a foreign place. Maybe you have a primary key in one entity,
but it’d be really helpful to pull that data into another entity. That’s where you get a foreign key. And we want to make note of these foreign
keys so we can better understand how our entities relate to one another. Let’s see how this plays out in our diagram. We’ve already established Customer ID as
the primary key for the Customer entity. But that same attribute is also over here
in the Order entity. Why? Because for each order we record, we want
to know exactly which customer placed that order. The Order entity is simply referencing the
Customer ID from the Customer entity. That makes it a foreign key here, so we’ll
mark it as such. And we can further show this relationship
in our diagram by adjusting this relationship to line up with the primary and foreign keys. Just move these crows feet to line up with
the PK and FK attributes. It reinforces the fact that this foreign key
in the Order entity is referencing the primary key of the Customer entity. Let’s review how this foreign key works
by looking at it in the Order table. So here are all our orders. If we look at a specific order, it has a Customer
ID of 30016. This is a foreign key because it references
the primary key in the Customer table. Now we know exactly which customer we’re
dealing with when we look at this specific order. So John made this order here, but look farther down. The same Customer ID is repeated because John
placed another order. Unlike a primary key, foreign keys don’t
have to be unique. They can be repeated in a table. If John’s an avid shopper, his Customer
ID is going to be repeated a lot. Another difference between primary and foreign
keys, is that there can be multiple foreign keys in one entity. Let’s say for each order, we also want to
know what product is being sold. We’d add Product ID to our table. And this is what it would look like in our
diagram. We just add another field, type in Product
ID, and since Product ID is a primary key over here, that makes it a foreign key in
this entity. Now we have two foreign keys in our Order
table. There’s also something called a composite
primary key. Composite primary keys are used when two or
more attributes are necessary to uniquely identify every record in a table. So let’s say we created a Shipment entity,
and it’s got these attributes here. And then let’s take a look at what the corresponding
table might look like in order to explain this better. Let’s say John ordered a regular snuggie
and then a zebra-pattern snuggie. It’s all one order, but they’re getting
sent in two different shipments. When we look at these two rows and each of
these attributes, we wouldn’t be able to rely on any single one to give us a unique
record. The Product Number is duplicated when someone
else buys that same zebra-pattern snuggie. So it’s not a unique record in this table. An order could be parsed into a couple different
shipments, like John’s was. So Order Number is also not unique. Charge Card Time would be duplicated if someone
else pays for their shipment at the exact same time. And same for Packing Time and Ship Date. All not unique. So it doesn’t look like any of these attributes
will give us the primary key we’re looking for. But what if we took two of these attributes,
and combined them to create a new, unique value? Like Product ID and Order Number. Take those two values for any instance, combine
them together, and you’ve got a value that won’t be repeated. That’s a composite primary key. Now the primary key for this shipment is different
than the primary key for this shipment. You could technically call this a compound
key because we’re using two foreign keys, but usually people just say composite key
as an umbrella term. Anyway, jumping back over to our diagram,
you note a composite primary key with multiple PK marks. That doesn’t mean there are two primary
keys. It means that both of these attributes are
needed to create a composite primary key. A couple rules here when making these. One, use the fewest number of attributes as
possible. Two, don’t use attributes that are apt to
change because that can make things messy. And you might ask yourself, why not just create
a Shipping ID attribute so we don’t have to deal with this scenario. Well you certainly could. And that’s totally valid. It’s actually quite a debate amongst
some people if you should even bother with composite primary keys. But it all depends on the database you’re
creating and if there are certain scenarios where a composite primary key makes sense. Another thing we can talk about here is something
called a bridge table. When you’re making your ER diagrams, it’s
important to ask yourself, “Is there anything else that I should be recording into the database?” Sometimes you’ll have two entities connected
to one another, but there’s more going on between them than you’re accounting for. That’s when you need to use a bridge table. To illustrate this point, I’m going to strip
down our diagram to just Customer and Product. Now couldn’t we just create a direct relationship
between these two entities? A customer can purchase zero or many products,
and a certain product can be purchased by zero or many customers. Sure. Conceptually, this works. But the way this is set up, you’re not going
to know when a customer purchased that product. You’re not going to know if they bought
all of them at once or if they returned to buy more at separate times. You’re going to be in the dark on many details regarding the interaction between these two entities. And this issue often occurs when you’ve
got a many-to-many relationship. That’s where a bridge table comes in. A bridge table allows for an intermediary
one-to-many relationship and gets you the information you’re lacking. In this example, a bridge table would be the
Order entity. Let’s put that back into the diagram. You can see how it breaks up the many-to-many
relationship, and now, each time a customer purchases a product, we’re going to have
a record of that interaction in our Order table. So as you build or modify your ER diagram,
make sure you’re asking yourself if you’re getting all necessary information and use
bridge tables to capture that data. If you want to start getting a bit more specific
with your ER diagram, you can do so by adding data types for each attribute. This can be really helpful, especially if
you’ll be doing some programming out of this diagram. So we use an entity shape with 3 columns,
and we’ll specify the type of data we want on the far right side. For Customer ID, we’ll return an integer. That’s going to give us a number like 501422. And all these attributes here will be varchar,
meaning you can use various characters and then set a character limit in parenthesis
like this. So for phone number, for example, it’s going
to have to be 10 digits to fit the standard phone format. There are several different data types and
things we won’t get into, but just know that you can include these in your ER diagram
if you want to get more technical. Now we’ve added a lot of detail to this ERD,
but what are we going to actually do with it? Building ERDs is a great way to conceptualize and visualize before you've actually built your database. But once you've built the whole ER diagram,
you're going to need to translate it into a database management system, or DBMS. Typically, you’d have to just reference
this diagram and then manually enter code to create the database. That’s a lot of work. But here, Lucidchart allows you to export the
diagram and it will automatically generate the code you need for your database. You can see the table commands here with the
entity it’s referencing. Here are the attributes for this entity with
the data parameters that we set. And it also notes our primary and foreign
keys. Then you just specify which database management
system you’re using, copy the code, and paste it in. Your database is ready to go. It's a total time-saver...and if you’re
a student with an assignment to make a diagram and a database, this is like cheating. It’s so easy. Conversely, let’s say you’ve already got
a database created, but you want to visualize it. Anyone who’s worked in a database management
system knows that it's not exactly easy to see what’s going on. But an ERD of that database gives you a visual
layout. Well, you can just import the database here. And Lucidchart populates all your tables. Drag one out and it automatically creates
the entity with the appropriate attributes, primary keys, and foreign keys. And if you drag out another entity, the correct
relationship will automatically connect between them. This is another huge time-saver. You can quickly visualize your database, make
changes in diagram form, and then pass those changes back into your database via code. Thanks for watching this tutorial on ER Diagrams. Please subscribe to our channel below to get more
helpful tutorials heading your way. Also, we’re always curious...what other tutorials
do you want to see? Please comment below and let us know. And don't forget, sign up
for a free Lucidchart account so you can start creating
your ER diagrams today.