Transcription
We've looked at the types of tables and columns available in Oracle database, but how do you decide what to store in each table and column? For example, do teddies and bricks go in separate tables or the same table? After all, they are both toys. And is a box of bricks one row? Or is each individual brick a separate row? And what about xylophones?
To help me decide, I need something to eat. It's pancake time! I love pancakes, so I want to go and cook some, but I'm all out of ingredients, so I need to buy some milk, eggs, flour, butter, and sugar. Your typical store will place each of these in their own location in the shop. That was a lot of work, and I only need a few spoons of sugar, not the whole bag. It saves me a lot of work if supermarkets made pancake kits. Then, instead of having to hunt down all the ingredients, I can get everything I need in one place.
These two ways of storing food are like two ways of storing information in your database. In the first approach, each ingredient is a separate row, possibly in separate tables. This is classic relational modeling. Every row represents a single thing. But as we saw, this can mean a lot of work to get all the things you need. With the kit, everything I need is in one place. This is like document modeling. Each row can store many things. This makes it much easier for me to find what I'm looking for.
So, document stores are the way to go, right? Sure, it makes your life as a consumer easier, but imagine you're the shopkeeper. How does this change things? See, pancakes just aren't pancakes without maple syrup to go on top, and bacon on the side, and some fruit. Should you include these in the pancake kit? That's a lot of extra expense for the weirdos who just want plain pancakes. And what if you just want a fried egg? You don't want to have to buy a whole kit just to get one egg.
Or what if you want to use the eggs for something else, say quiche? You can start selling quiche kits, and kits for meringues, and omelets, and all the other recipes that include eggs. But do this, and you have some interesting problems. Imagine there's a worldwide chicken shortage. The price of eggs has gone through the roof. So, to keep making money, we need to increase the price of everything that includes eggs. That's a lot of work.
And the problems with pancake kits don't stop there. My daughter also loves pancakes, but she's lactose intolerant. So I need a kit with non-dairy butter and milk. But what kind of milk do you use instead? Almond? Soy? Or Nigerian Dwarf goat milk? And what about the people with a gluten intolerance? If you sell a kit with every option for every ingredient, you soon have a whole aisle dedicated just to pancakes. Scale this across all the different recipes you can buy, and you need a store the size of a city!
If you just stock all the ingredients separately, you can let your consumers decide how to combine them. This makes your life as a shopkeeper much easier. And we haven't talked about one of the biggest issues with document modeling yet; keeping your data consistent. Say you're building a film database storing details about the title, genre, who appeared in it, and so on. You decide you're going for document-based storage. So you create a film table. Each row stores the documents with all the details of a film. But do this, and you're storing actor names many, many, many times. If there's a mistake in one, how do you know which is correct?
It's better to store the details of films and actors in separate tables. This is the basis of relational modeling. You only store each fact once, making conflicts impossible, and if you make a mistake, you only need to make changes in one place.
So, how do you decide which things go in the same table, and which things go in a different one? This is a skill that's part art, part science. Many times, it's obvious. For example: Pancake ingredients and teddy bears are clearly different things. So you store the details of these in separate tables. But what about the question I asked at the start? Teddy bears and bricks are both toys, so do you put them both in the same table? Or do you have a separate table for teddies and a separate table for bricks?
Ultimately, it comes down to what you're storing and how you'll use the data. My daughter only cares about what toys are called and what type of toy they are, so we're storing the same information about both teddies and bricks. So we can get away with just one table and have a row for each teddy and a row for a box of bricks. But imagine you're the manufacturer. For the bricks, you need to know the color and dimension of each. So you need a row for each brick storing these facts. A teddy has completely different information you need to know, such as how much stuffing is used. So it makes sense to have separate tables: one for bricks, and one for teddies.
To decide how you're going to model your data, you need to speak with the people who'll use it to find out what they need to know about the things you're storing. Just remember, everything's a trade-off; there are very few one-size-fits-all solutions. What works for someone else may not work for you. Thanks for watching. Subscribe to my YouTube channel to learn more about database developments and see SQL magic.