Next in this series
Plan a table structure
- Think about the purpose of your database. Ask yourself the questions and issues raised by the course. Write down your purpose or mission statement, and expect to revise it.
- List the data you want to capture. In the case of an asset-tracking database, you'd capture information about each asset, the manufacturer or supplier of each asset, and possibly the companies who repair or support each asset. The exact list depends on your needs.
- Ask yourself how the data you listed falls into groups. Create as many groups as you think you need.
- Ask yourself if each group contains unique data. Put another way, start looking for redundant data — make sure each group contains data about a single subject. If they don't, break them up until they do.
- List the fields for each table. This is another place to look for redundant data and place that data in its own table.
- Stop and ask if the location data should be in its own table. How often will you repeat each location? Some databases store locations in a separate table, some don't. Will you need to search for assets by location? If so, then add a Location table to your list. List each field for each table until you have a way to store all the data you need.
- Now add a primary key field to each table. This can be as simple as adding an “ID” field, such as “AssetID” and “SupplierID” to each table, and setting those fields to the Autonumber data type.
Important Every table must have a primary key.
Finally, if you only need to track a few redundant items, you can create lookup columns that contain short lists of values instead of tables.
Additional questions to ask about your data
These questions can help you structure your data:
- Does each field contain a single piece of data? For example, it's usually better to store first and last names in separate fields.
- Is each record unique, or is a record repeated somewhere?
- Are any details, or any groups of details, repeated in more than one record or table?
- Can you easily make changes to one record without changing another record?
- Does each record contain all the details that belong in that record?
- Does each record contain only the details that belong in that record, specifically appropriate to the record's identity?
- Are any fields dependent on other fields in the table?
A bit more about relationships
You can create three types of relationships between tables. The link in the See also section takes you to information about planning and implementing relationships.
- One-to-one: This type of relationship exists when a single record in one table is related to a single record in another. For example, one record in the Employees table would relate to only a single record in the Commissions table.
- One-to-many: This is the most common type of relationship.
- Many-to-many: This type of relationship exists when a single record in Table A can be related to many records in Table B, and a single record in Table B can be related to many records in Table A. For example, any number of students can take a given course, and a course can contain any number of students.
Reminder You create relationships by using the primary key from one table as a foreign key in another table.
Characters to avoid using in field names
Don't use the following characters or symbols in field names or object names. In Access, objects are any of the components that make up your database. Tables, queries, forms and other components are all "objects."
- Periods (.)
- Forward slashes (/)
- Asterisks (*)
- Semicolons (;)
- Colons (:)
- Exclamation points (!)
- Pound signs (#)
- Ampersands (&)
- Dashes (-)
- Question marks (?)
- Double quotes (")
- Single quotes (')
- Dollar signs ($)
- Percent signs (%)