Creating an Access Database - Step 2: The Tables
OK, we've sat down and thought about the design of the database, and now have some idea about what we are creating. We can now switch the computer on, and start Access. Now, if we thought about the design properly, we should only need to deal with the tables and relationships once.
Before we actually create any tables, it is an idea to change an option so that Access doesn't create duplicate table indexes. From the database window, select Tools|Options, and then click on the Tables/Queries Tab. Remove "ID;" from the AutoIndex on Import/Create. If we had left this in, Access would automatically create an index on any field with "ID" in the field name. Whilst this might be OK normally, I prefer to end any primary/foreign key with "ID", and if this is a primary key, Access will create a second index as well as the primary key index. Although Access allows you 32 indexes in a table, and you probably won't have that many fields in a tables, let alone indexes, the effects of the second indexes will become more apparent when you create queries.
Now we can start by creating the tables. I'm just going to list the attributes for each table here:
When creating a table, I prefer to have the primary key as the first field, followed by any foreign keys, and then the rest of the fields. I normally try to use descriptive field names, made up of two or more words joined together without spaces. This has several uses. Firstly, it limits the chance of me using a reserved word, for example Date, and secondly, it helps me when I come back into the database several months later, by seeing a field that describes what it is.
Having created the tables, we can now relate the tables. From the main database window, select Tools|Relationships, and add all of the tables to the screen (after all, there's not much point in having tables that aren't related to anything else).
Create the following relationships between the tables:
The resulting relationships should look something like:
Copyright & Disclaimer
Last modified at 06/06/2006 15:02:10