What is a database?

A database is just a collection of building blocks that work together. Lets look at some blocks.

Customer     Order     Shipment     Invoice

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.

        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.

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.

        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.

        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.

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.

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.

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.

We'd have several screens in our order application but the first would be a "Main Menu" with some buttons.

                    Main Menu
                    
         Enter A New Order        Ship An Order 
        
         Revise An Order      Open Order Report 
        
        

When you click on the "Enter A New Order" button you'd get a screen something like this:

                    Order Entry
                    
        Pick A Customer:
                                 v   NEW 
        
        Pick A Part Number:
                                 v   NEW 
        
        Enter A Qty:
                
                    

And then, when you click on the "Ship An Order" button you'd get a screen something like this:

                    Ship Order
                    
        Pick An Order Number:
                                 v   Ship 
                    

Let's say we entered order number "2" and clicked the "ship" button. We'd probably get a screen like this.

                  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
        
         Print Pack Slip 
                    

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.

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.