Before you start using Access, take the time to answer a few questions:

 

• Why do you need a database?

You want to keep track of the expenses made by you and your employees.

• Who will be using the database?

Any employee of Warner Cousins will have access to this database.

• What kind of data would be extracted from the database?

Total expenses of the company, total expenses by each employee, expenses by each category.

 

Once you have answered these questions, it is time to decide how to design the tables for your database. What fields of data do you need? What data types will the fields need to be? What tables would be important? Which fields will go in which table? Do the placements make sense?

Next comes the planning of relationships between the data. A big list of numbers doesn’t mean much by itself, but when constructed based on other data, it becomes meaningful. And finally, make sure that you talk to everyone who will be using the database – make sure that everyone (especially the finance department!) will be able to get the data they need.

In the case of your animation studio, let’s examine the details:

You will obviously need an Expense table: who made the purchase, what did they buy, how much was it, and when did they buy?

Chances you already have a listing somewhere of who works for you:

• SIN (or company ID #)
• Name
• Address
• Phone Number
• Company Position

Now take a step back – we have stumbled across a relationship! We can use the SIN (or some other identification number) of each employee to tie their purchase to their personal information. 

In database design, your most powerful tool is not the computer, but rather a piece of paper and a pencil (and a big eraser). Not only can you easily change the information you might need, but you can also visualize the information. Consider the following diagram:

 

It might not look like much, but we have a database. It contains fields, records, a primary key for each record in each table, and a relationship between the data. We can see that employee 2 has made two purchases, and employee 1 only one. This might seem silly for an example of this size; why not just say Elmer Funn instead of an employee ID? But imagine that your company has grown to employ thousands of people with hundreds of expenses a day – that becomes a pile of data in a big hurry! You might have three or four Elmer Funns by now, so using a unique number to identify each employee becomes much more practical.