Unlock the Power of a Contact Management Database for Planning

How to create outlook signature

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.