Determining Your Entities


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?

we sell toys. we need a toys table.

2.

Do you have an ID or product number for each toy?

yes, a combination of letters and numbers. we need a text toyid field as a key field in tbltoys.

3.

Do you make the toys or purchase them from vendors and resell them?

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.

What are the differences between manufactured and purchased toys?

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.

Are raw materials purchased from different vendors than toys to resell, or could one vendor sell you both materials and finished toys?

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.

When you make the toys, is this done in your own workshop or factory, or contracted out?

done in our workshop. no need for a contractors table.

7.

Do you have just one workshop, or several?

just one. don t need a lookup table for workshops.

8.

Do you do anything else other than selling toys?

yes, we also repair broken toys. need a repairs table.

9.

Just the ones you sell, or others too?

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.

Are the repairs done in-house, or contracted out?

in-house only. we need a table of employees, with a field to identify those who do the repairs.

11.

Do you send out catalogs or other promotional materials?

yes. we need a table of customers, and also a table of potential customers or leads.

12.

By mail, email, or both?

both. the mailing list table(s) should have both the mailing address and email address.

13.

Do you sell toys in a store, by mail, or over the Internet?

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.

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.




Expert One-on-One(c) Microsoft Access Application Development
Expert One-on-One Microsoft Access Application Development
ISBN: 0764559044
EAN: 2147483647
Year: 2006
Pages: 124
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net