I'm going to give you a high-level overview of database architecture understanding principles of database management will help you as you're getting into the more technical concepts. So let's say you're 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 a spreadsheet and start putting in things as orders come in maybe it looked 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 married buy something else like three 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 customers address and if your specialty cat store got enormous ly popular these issues would just escalate Mary calls to ask about one of her orders and when you pull up her information you get three different addresses all these disorganized orders and you're not even sure you're looking at the correct customer because there are three different Mary Johnson's in your spreadsheet you could see how this might lead to a messy situation shipmates could get sent to the wrong place customers might get mixed up and 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 all of your customers and then a separate table that lists out all your products and then another table that records each of your orders this separates the data you're pulling 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 it's these connections that form the database let's dive in and take a closer look to see what these interactions look like we'll start in the custom her table let's say someone goes to our online store 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 rerecord 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 we're using now is far more organized than our single spreadsheet from earlier not to mention it's far more robust and handling large amounts of data and information but database management systems typically aren't very good at visualizing the tables and connections within a database 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 to 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 card 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 e Rd and that's a super fast process with lucid charts ERD import tool just run a query of your database and lucid chart 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 on board someone who's new to your database they can look at an ard and see how the whole thing works on the flipside 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 concept 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 is that when you're done concept agrammatism annually 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 gives you a bit more context as to why we use databases and how they relate to entity relationship diagrams to learn more about entity relationship diagrams and our other tutorials like primary keys foreign keys attributes and cardinality click here click here to start creating your own ER diagrams today.
