Hi, my name’s Taylor and I’m going to
walk through a high-level explanation of database architecture. Understanding the underlying logic and concepts
behind database management really makes it easier to understand the more technical aspects
of ERD further down the road. So let’s say you were opening your own online
store for something like specialty cat accessories, and you want to be able to keep track of all
sorts of information surrounding your sales. Most people would just open up a spreadsheet
and start putting in things as orders come in. Maybe it’d look like this. Mary’s our customer, she buys a cat leash,
and you capture all this information. And you just record this information for each
of your customers that trickle in. But maybe later Mary buys something else,
like 3 cat leashes, and at this point she’s moved to a bigger place to have room for all
her cats, so her address is different. Now you’ve got redundant information, some
contradicting values for your customer’s address...and if your specialty cat store
got enormously popular, these issues would just escalate. Mary calls to ask about one of her orders,
and when you pull up her information, you get 3 different addresses, all these disorganized
orders...and you’re not even sure you’re looking at the correct customer because there
are 3 different Mary Johnsons in your spreadsheet. You could see how this might lead to a messy
situation. Shipments could get sent to the wrong place;
customers might get mixed up; the wrong products could get sent to the wrong people. So how would you resolve this? Instead of having just one massive spreadsheet,
you’d separate the information into different bite-sized tables. So with our cat store, for example, you might
start by creating a table that lists out all your Customers, then a separate table that
lists out all your Products, and then another table the records each of your Orders. This separates the data you’re pulling in
in a much more efficient way. So here’s what your Customer table might
look like. You can see we’ve got Mary again, but now
she won’t be repeated into several different rows. Any change to her address, contact info, or
even name can be updated in this one consolidated place. The Product table would have all your cat
accessory inventory. As you add or remove items, this would be
the one place where you’d make those changes. And Orders would keep track of every single
sale you make. Now these tables, although separate, have
connections to one another, and this is what forms a database. So let’s take a look at what this interaction
might look like. We’ll start in the Customer table. Let’s say someone goes to our online store
and makes a purchase. It’s a guy named Ronald, and he’s in the
market for a cat costume and buys one from our store. When he checked out, he entered all his contact
info, and we’ve recorded it in this Customer table and assigned him a customer ID. Let’s move over to the Product table. This lists all our inventory, and here’s
the cat costume he wanted. We keep track of it with a few fields here,
like product ID, quantity in stock, and product type. And then when Ronald actually ordered the
cat costume, we record that specific purchase information in the Order table. Here you can see we pulled in the customer
ID from the Customer table, so we know it’s Ronald. We also pulled in the Product ID from the
Product table, so we know that he purchased this cat costume, And there’s other data
in here that tells us about the date of the sale, shipping address, quantity, etc. It’s pretty obvious that this system is
far more organized than our single spreadsheet from earlier. That’s why you want to create different
tables and connect them within a database. But database management systems typically
don’t give you the best options for visualizing those connections. It’s all in the programming language and
it’s hard to see where the connections are, and where improvements can be made. That’s where Entity Relationship Diagrams
come in. It’s a visual way of looking at your database
structure. Each table translates into an entity. And your column categories, like customer
name, address, purchase date, etc., are listed as attributes in their respective entity. Finally, the programmed connections between
your tables, like how Ronald’s order referenced a specific product ID and his customer ID...those
are visualized through relationship lines. So imagine if your database was far more fleshed
out than our simple example. Like if you had separate tables for Shipping
Address, Billing Addresses, Credit Cards, Shipping Info, etc. Trying to make sense of a large database when
you’re in the database can be very taxing. It’s much easier to visualize it through
an ERD. And that’s a super fast process with Lucidchart’s
ERD import tool. Just run a query of your database and Lucidchart
automatically imports the tables that you can then drag out as entity shapes. And the relationships between entities automatically
connect as well. So you quickly create a visual representation
of your database and then it’s so much easier to spot database errors, you can see where
you’re getting duplicate data, and it’s way easier to onboard someone who’s new
to your database. They can look at an ERD and see how the whole
thing works. On the flip side, let’s say you don’t
have an existing database. You’re starting from scratch and want to
build one...well, ERD is a great tool for concepting. You’ve got an idea for how your database
is going to work, and you flesh it all out in a diagram. And the awesome thing is that when you’re
done concepting, the diagram itself can be translated into the code that forms the actual
database. You don’t have to manually recreate your
concept in database form. The entities automatically transform into
tables, the attributes to columns in those tables, and your relationships get translated
into coded connections. Hopefully this gave you a bit more context
as to why we use databases and how they relate to Entity Relationship Diagrams. If you want to learn more about ERD, click
over here. Our tutorials cover entities, attributes,
cardinality, primary and foreign keys, and much more. And click here to start making your own ER
Diagrams today.
