Designing databases - Amazon Web Services (AWS) Tutorial

- I have a mantra. And that is requirements are all that matters. And what do I mean by that? Well, this applies to every area of information systems that I've worked in. If you start any project without good requirements you are not going the results you need. So in the beginning of a project the only thing that matters is the requirements. We have to make sure we understand the requirements so that we can deliver what is actually needed by the organizational users. Now, in the realm of databases the goal of database design is a resulting database that gives the organization the information they need. That sounds simple. But amazingly so many people do not start a database design project this way. Instead they just jump in thinking they know what the organization needs. Thinking they understand what should be in that database. Without really doing any kind if investigation to find out how people actually going to use that system. So, database requirements really begins with interviews, talking to users, talking to managers, finding out what people need out of the system. And then from that, we can begin to answer some key questions. First of all, what entities will be represented in the database? And remember, an entity is a thing that's going to be represented in the database. A user, a customer, a product, a process, a value from some monitoring system. It's just something that needs to be in the database. That's an entity. And then how are the entities related to one another? Is the data relational in nature? So this is going to help you decide what kind of database to create. If there're no relationships among the data, then maybe a NoSQL database is the right way to go. But if it is relational in nature, for example, a customer is a customer of a particular salesperson. A customer places orders. The order includes products. These are all different relationships. And so relationships drive us into a more relational model database. How many of each entity is expected? Are we expecting hundreds of records in one table, but thousands or 10s of thousands or even millions of records and another? This is going to drive how we design the database. How many users will be writing data versus reading data? So writing data is about transactions. They're making changes to the database, adding new records, modifying existing records, deleting records, and so forth. But the read users well, they're just querying the database. And that's not as intensive because we don't have to do as many writes to disk. But either way, we need to know how much of each is going to occur, so we can design the database optimally. Now I talked about relational databases. And we've talked about them elsewhere in this course. So this brings us to finally talking about the relationships themselves. These links between different entities in our database. And there are really... Well there are actually four different kinds, but we generally talk about three because the second one could be divided into two. I'll explain that when we get there. So first of all, we have one to one. Usually represented with 1:1. And so that means that there is one particular record in one table that is linked to another particular or specific record in another table. A one to one relationship. Then we have the one to many relationship. Now this is the one that kind of covers both. One to many, and many to one. Now the reason I say that is because really a many to one is a one to many in reverse. Now we usually represent one to many as 1:N the N being that typical variable for some number. It could be zero, it could be 1 million. So it's some number. And in this case, exactly one record in one table matches multiple records in another table. We also have many to many. Now many people do not like the many to many relationship concept. The reason is how we actually implemented in databases is not that we have one table, and then another table, and many things in this table are linked to many things in this table. No, instead, what we have is many to one, and one to many, or many to one and one to many. What's this thing here in this virtual invisible middle that I'm talking about? This is your linking table. So in in many to many relationships, you have a linking table between two other tables. But many to many ultimately means many records in one table are linked to many records in another. So one to one think of it like this. One student has one locker. So if you've got to students table and a lockers table, that would be one to one. But there could be many students in one class. So that's a many to one or one to many. And you could have many to many. And that is to say that many customers have bought many items. And so you can have a linking table to link that. You could also have, many authors have written many books, because think about it, you might think that's just a one to one, but the reality is that there could be multiple books written by the same multiple authors, resulting in a many to many kind of relationship. That's not usually tracked in most book databases, but it could be. So the concept then of all of this, is to make sure that we can select the right database type. If it's relational in nature, you need a relational database, but you have to answer some questions. What are the size, performance and application requirements? So size requirements, how big is this database going to become? Performance requirements. We're asking what is the demand that's going to be placed on the database based on the number of users the size of their queries or transactions and the amount of throughput and processing capability needed? And then the application requirements, it has to do with the kind of database. So it's not just relational, but maybe it has to be MySQL. Maybe it has to be PostgreSQL, maybe it has to be Microsoft SQL Server, et cetera. You need to know those as well. And finally, if it's non relational in nature, you need a NoSQL database. You still have the same questions though, how big is it going to be? How much performance do you need? And what are the application requirements? And so at this point, you've got the information you need to sit down and actually design a database