Page 6 of 13PREVNEXT

Design the tables for a new database

Fields and possible relationships

You're starting on the gritty details

The next step in your design is to list the fields for each table. In an Access table, columns are called fields and individual records are called rows. As a rule, each field in a table is related to the other fields. For example, in a table of business contact data, you'd typically have fields for first name, last name, company, phone numbers, and more. Each field must be related to the others, and each field must only apply to business contacts. That set of related fields is called a relation, and that's where we get the term relational database.

You plan your fields by deciding the specific information each of your groups should capture. Again, you can refer to your existing data — the spreadsheet, a ledger, or even your card file. You can also open an existing database, such as one of the Access database templates, and see which fields it uses.

For your asset database, you'll probably want to list each item and information about each item, such as purchase dates and costs. As part of this, try to reduce each field to its smallest logical component. In a good design, a field represents a single piece of data, and the name of the field clearly identifies that data. For example, your tables should have separate fields for first and last names because that makes it easier to find and sort your information.

Also, ask yourself if you need to record international data. If you do, you'll need fields for Country, plus Region instead of State, and Postal Code instead of Zip Code.

As you work, you may find yourself wanting to use data from one table in another. For example, the picture shows that the Assets group includes fields for suppliers and support. That's natural — you're seeing how you need to relate your tables, and we'll discuss those relationships in just a bit. For now, include all the fields you think each table should have.

Note    If a field name contains more than one word, don't separate each word with spaces. They can make it harder to create other components, such as Visual Basic for Applications code and a type of formula called an expression. If you need to use multiple words in a field name, eliminate spaces and capitalize the first letter of each word, or separate the words with an underscore. You'll see how that works in the practice.

Finally, in case you're wondering, you don't plan rows. Those come naturally as you enter data in your fields.

Page 6 of 13PREVNEXT