Let's Build A Database
What is a database?
A database is just a collection of building blocks that work together. Lets look at some blocks.
We call each of these blocks a table but you may know them by their other name: spreadsheet. A spreadsheet shows data organized into columns and rows.
The Customer Table
id CustomerName Address City State Zip Phone POC 1 Billie Industries 6 Happy Street Biltom TX 76188 817-555-1212 Bill Smith 2 JJ Ajax Co. 3 Stray Street Pultom TX 76189 817-555-1212 Pam Smithe 3 Pulman Dexon LLC 4 Filly Street Rinton TX 76183 817-555-1212 Bob Neet 4 Filtre Century 2 Calta Street Exeton TX 76182 817-555-1212 Rob Clean 5 Big Corp Limited 7 Delta Street Bifton TX 76181 817-555-1212 Sandy Lane
So what is a database? A database is a collection of tables or spreadsheets that work together.
How do tables 'work together'?
A database is a collection of tables that work together to reduce duplication of data.
Let's look at an example. We have a spreadsheet with our Order data. In a way, it looks a lot like the Customer table.
The Order Table (Spreadsheet Version)
OrderNo CustomerName Address City State Zip Phone POC ItemNo Qty 1 Billie Industries 6 Happy Street Biltom TX 76188 817-555-1212 Bill Smith MS6604-22 100 2 JJ Ajax Co. 3 Stray Street Pultom TX 76189 817-555-1212 Pam Smithe MS6604-12 200 3 Pulman Dexon LLC 4 Filly Street Rinton TX 76183 817-555-1212 Bob Neet MS6604-33 100 4 JJ Ajax Co. 3 Stray Street Pultom TX 76189 817-555-1212 Pam Smithe MS6604-44 300 5 JJ Ajax Co. 3 Stray Street Pultom TX 76189 817-555-1212 Pam Smithe MS6604-11 100
Do you see the duplicates? Pam at JJ Ajax Co. has made three different orders so our order table has her company, address, phone and point of contact name duplicated three times. That's a lot to type, and it's a lot to read. Eventually this spreadsheet is going to get really big and it's going to slow down and become difficult to use.
Databases were invented to solve these problems. What we do is remove all the data that's already in the customer table and just pop in one column with the Customer ID. Let's redesign our Order table and see what it looks like.
The Order Table (Database Version)
OrderNo CustID ItemNo Qty 1 1 MS6604-22 100 2 2 MS6604-12 200 3 3 MS6604-33 100 4 2 MS6604-44 300 5 2 MS6604-11 100
But wait! We have solved the problem of duplication of data but now we can't use the order table! Let's say we want to ship an order. We have our parts and they are all boxed up and ready to go. Who do I ship it to? That information is gone and I can't see it from the order table anymore. That's bad. We need something to go out and gather all the related information for an order and put it all on the screen at one time.
But that's not all. We have another problem. We're looking at too much information at once. If a user goes to ship an order by looking in the order table they need to make sure they ship the part to the right customer. If they could only look at one order at a time there would be much less of a chance of them shipping say, the Item for order "2" to the address for order "3".
Fortunately for us there is a way to pull all the information for just one order into one screen. That's called a query.
What is a query?
A query is a way to put a question to a database.
Let's solve our order problem. We want to ship just order "2". So we only want to see order "2", but we also want all the customer information so we can fill out the shipping address on our package.
The Order Query
SELECT OrderNo, CustomerName, Address, City, State, Zip, Phone, POC, ItemNo, Qty FROM Order LEFT JOIN Customer ON Order.CustID = Customer.ID WHERE Order.OrderNo = 2;
OrderNo CustomerName Address City State Zip Phone POC ItemNo Qty 2 JJ Ajax Co. 3 Stray Street Pultom TX 76189 817-555-1212 Pam Smithe MS6604-12 200
The order query is in a language called SQL. SQL is very simple in comparison to most programming languages but we don't want to learn SQL to solve our order problem. If we did, then only people who know how to program would be able to ship packages!
So we have a new problem. How do we make it so anyone can ship our packages? For that we use an application or app for short.
What's an application?
An application is a way to present the user with easy tasks or questions while hiding the complexity of how databases work.
A good application has three big advantages over spreadsheets.
Applications are reliable.
All good applications use some form of database to hold data. Databases are faster than spreadsheets and use less disk space. Databases are easier to back up than files and don't often go missing.
Applications save time.
All good applications offer automation. For example, when you ship something on a spreadsheet you need to mark the order as shipped, then you have to mark the inventory down. An application would do both of these steps at the same time. Applications can also be programmed to offer reports and other forms of data on demand or on a schedule, freeing you to do more important things like make new sales.
Applications reduce errors.
All good applications reduce errors. Many errors are prevented by making sure entry data is clean, for example, making sure a date field only allows dates to be entered. If you prevent bad data from going in you can more easily prevent bad data from coming out.
What would our Order application look like?
We'd have several screens in our order application but the first would be a "Main Menu" with some buttons.
The Main Menu
When you click on the "Enter A New Order" button you'd get a screen something like this:
The Order Entry Screen
Order Entry Pick A Customer:Pick A Part Number: Enter A Qty:
And then, when you click on the "Ship An Order" button you'd get a screen something like this:
The Ship Order Screen
Ship Order Pick An Order Number:
Let's say we entered order number "2" and clicked the "ship" button. We'd probably get a screen like this.
The Ship Order Screen
Ship Order #2
OrderNo CustomerName Address
2 JJ Ajax Co. 3 Stray Street
Pultom, TX 76189
Phone POC ItemNo Qty
817-555-1212 Pam Smithe MS6604-12 200
Once we click the "Print Pack Slip" button our app would send the packing slip to the printer and maybe send the info to UPS and print a UPS label.
Let's build a database!
We've seen what a database is used for and gotten some basic ideas of what an application can do. Before computers every business in the world used spreadsheets but they called them by different names like ledger, chart of accounts or even just "the books". Applications have made things faster and easier while reducing errors.
If you're using spreadsheets to run your business your next step is to send us your spreadsheets and we'll turn them into an app that will save you time and make you money.