Building a database on the fly is an easy way to ensure you wind up with a terrible final design. A much better approach is to plan ahead and carefully think through the requirements of the system you're about to build that accurately models the real-world data you need to store.
- [Instructor] You wouldn't arrive at a building site with a load of lumber, and start hammering together a house without a proper blueprint. Constructing a database should be treated in the same way. Building a database on the fly is an easy way to ensure that you wind up with a terrible final design. A much better approach is to plan ahead, and carefully think through the requirements of the system you're about to build that accurately models the real world data that you need to store. All relational databases attempt to create a model of some real world phenomenon. The first steps in creating your personal database model is to identify what facts, or what pieces of information you need to store. Now I've seen many people begin a database project by listing out the information that they already have and they need to provide a home for. This can be a good starting point, but I challenge you to think about your data needs in more detail. When determining what tables you need and what columns they should contain, it's sometimes helpful to work backwards. Instead of looking at what you want to put into the database, ask yourself, what is it that you want to get out of the database? What types of analysis or reports are you expecting to be able to review? What would be helpful information to know in order to make better business decisions? These types of questions can help guide you in creating a list of information that you need the database to store. Then you can design the system to be able to provide the results that you desire, rather than to simply respond to what you already have. Here's an example from my past. I used to manage a couple of movie theaters, and one of the pieces of information that we tracked was the daily high and low temperature, and whether it was raining that day or not. Now that wouldn't seem like an obvious thing for a movie theater to want to keep track of, but over time it became a very valuable and useful dataset to have. And it allowed us to make projections about expected attendance based off of the weather. Having the ability to store weather information and attach it to historical transaction data made the database more useful. That's just one example of how thinking about the results that you want to achieve could lead to a design that might not be obvious from the beginning. Once you have a list of the pieces of information that you want the database to store, you need to start thinking about how those pieces are going to get grouped together into tables. Look for common themes in the effects that you've listed. One approach that you might find helpful is to think of the tables as nouns. The people, places, and things that you're collecting data about. Then each column in the table becomes adjectives, the descriptive attributes about each table's topic. When organizing your thoughts on the design of the database that you want to build, you're going to want to take notes. Low-tech solutions like sticky notes that you can move around on a wall or index cards that you can shuffle around on your desk, can work really well for this. You can write each attribute that you want to store on a card and then stack them into piles to represent tables. This will let you evaluate whether a table's contents make sense, or if it would be better to reorganize the columns into a different arrangement. Another approach is to draw a diagram, or what's more specifically called an entity relationship diagram. You can use pencil and paper for this or a software drawing tool that'll help you organize your database's structure. Since at this stage you're not concerned with the actual records that are being stored, just the makeup of the table and columns, the standard convention is to place the name of the table at the top of a box, and then list out the columns in that table below. In this diagram, I have the design of two tables mocked up, one for customer data, and one for orders. Each table only includes columns that pertain to the theme of the table. You'll notice that both tables have a column named CustomerID. At first glance, this might look like a data redundancy issue, but if you think about how the data is used in each context, you'll realize that the two columns represent very different uses for that information. In the customer table, the CustomerID will uniquely identify each individual customer, while in the orders table, it identifies the customer that placed the order. Because the values stored in each of these columns will be the same, we can use them to join or relate these two tables together, to create a relational database. In a diagram, you can simply draw a line connecting these two fields. This tells the system that the information in one table can be used to give more context to the information found in the other. In this case, we can find all of the orders placed by a specific customer. What I've just drawn is called a one to many relationship. We'll get into what that means in more detail later on in the course. But for now, you could read the diagram from left to right and say that each one customer can have many orders. Or if you read it from right to left, each order can only have one customer. There's a couple of different ways to note this relationship on the diagram. One way is to use something called unified modeling language notation, or UML. This will place a one on the line on the left, and a letter N on the right. The N is mathematical notation for any number. Some relational database management systems use a one to infinity symbol notation instead, to mean the same thing. And still others take a more diagrammatic approach and use something called crow's foot notation. This'll place a single slash on the one side of the line and a forked branch that resembles a bird's claw on the many side. Any way you draw it out means the same thing, though. Each customer can have several orders, and each order can only have a single customer. And that's the basics of creating an entity relationship diagram to help you with the development of your relational database. Your databases will almost certainly have more tables, but they'll all be deigned and connected in the same way. There'll be more details that we can add in later to better define the structure of the database, but this is enough to get started.
- The basics of data storage
- Choosing an entity-relationship design tool
- Using primary keys to identify records
- What to consider when naming objects
- Creating a unique constraint
- Establishing table indexes
- Relating tables with foreign keys
- One-to-many and one-to-one relationships
- Writing SELECT queries in SQL