The first task in setting up a database is determining what things it works with and how they work with each other. (The technical term often used in database literature is entity, but as far as I am concerned, thing works just as well.) If you are developing an application for a client, there may be an existing database. Depending on the skills of the person who created the database, this may be more of a problem than a helpful first step.
As an example of how to figure out the things your application needs to work with, following is a hypothetical example of a client who wants an application to manage his business, called the Toy Workshop. Let’s start by asking the client some basic questions:
1. | What does the business do? |
|
2. | Do you have an ID or product number for each toy? |
|
3. | Do you make the toys or purchase them from vendors and resell them? |
|
4. | What are the differences between manufactured and purchased toys? |
|
5. | Are raw materials purchased from different vendors than toys to resell, or could one vendor sell you both materials and finished toys? |
|
6. | When you make the toys, is this done in your own workshop or factory, or contracted out? |
|
7. | Do you have just one workshop, or several? |
|
8. | Do you do anything else other than selling toys? |
|
9. | Just the ones you sell, or others too? |
|
10. | Are the repairs done in-house, or contracted out? |
|
11. | Do you send out catalogs or other promotional materials? |
|
12. | By mail, email, or both? |
|
13. | Do you sell toys in a store, by mail, or over the Internet? |
|
Answers
1. | We sell toys. We need a Toys table. |
2. | Yes, a combination of letters and numbers. We need a text ToyID field as a key field in tblToys. |
3. | Both. We need a table of materials used in manufacturing toys. We might need two tables for toys—one for toys purchased for resale and one for toys manufactured in-house. We need to determine whether the two types of toys are different enough to require different tables or whether they can be stored in one table, with different values in a few fields, and a Materials table, for toy-making materials. |
4. | For purchased toys we need to record the vendor name, vendor product number, purchase price and purchase date; for manufactured toys we need to know how much of each component is used, the labor costs, and when they were manufactured. Sounds like we could use a single Toys table, with a Yes/No Purchased field to indicate whether the toy is purchased or manufactured; that field could then be used to enable or disable various controls on forms. We also need a Vendors table, to use when selecting a value for the VendorName field, and a Materials table, for toy-making materials. |
5. | Most of the vendors we use sell only finished toys; some sell only materials, and just a few sell both materials and finished toys. Then all the vendors could be stored in one table, with Yes/No fields to indicate whether they sell finished toys, materials, or both. |
6. | Done in our workshop. No need for a Contractors table. |
7. | Just one. Don’t need a lookup table for workshops. |
8. | Yes, we also repair broken toys. Need a Repairs table. |
9. | Our own and other similar toys. We can’t just identify the repaired toys by ToyID; we’ll need an AutoNumber field to uniquely identify toys made or purchased elsewhere that come in for repair. |
10. | In-house only. We need a table of employees, with a field to identify those who do the repairs. |
11. | Yes. We need a table of customers, and also a table of potential customers or leads. |
12. | Both. The Mailing List table(s) should have both the mailing address and email address. |
13. | From a factory store and by mail or phone. No Internet sales yet, but maybe in the future that will be added. We need an Orders table, with a field for sale type. Customers should be selected from a Customers table, with a provision for entering a new customer on the fly when taking an order. Since mail or phone orders will require both a shipping and a billing address, we need a linked table of shipping addresses. |
From these answers, we know that the application needs primary tables for the following things (these are the application’s entities):
Toys
Categories
Vendors
Customers
Shipping addresses
Mailing list
Materials
Repairs
Employees
Orders
Additionally, a number of linked tables will be needed, to store data linked to records in the main tables, and some lookup tables will be needed to store data for selection from comboboxes, to ensure data accuracy.