Table design planning is the most important part of building your Microsoft Access database, and should start before you open Access.
For each of the suggestions I will be using parts of Microsoft's Northwind Traders Sample Database 2007, so you can download it and follow along for yourself. (See above for the link).
The most important part of creating your database begins before you even open Microsoft Access. The more planning that you do before development, the more smoothly the development will go, with less errors, and the better the final result will be. Proper requirements gathering at the front end of the project helps to prevent scope creep and gold plating throughout the project.
Before continuing I would recommend reading the Strategic Planning section to begin the process.
Look at the final results that you would like to get out of your database, (invoices, labels, statements, reports), and make sure that all of that information is included in your table for your database.
There are many different ways to plan your database. One of the easiest, I believe, is to use Microsoft Excel. Once you determine what information you want to track or report you can use Excel to list the titles, or fields that you will create in your database.
One thing to remember is that you need to keep all of the information that is related in the same table. All of the customer information or fields will be together, all of the employee fields will be together, etc. The only exceptions may be fields that will use a large amount of memory such as attachments or memo fields probably should get their own table, and if a lot of data is stored they should be in their own linked back-end database. Also fields that you will want to have multiple options to select will require their own table, such as Order Status in the example below.
One thing to note about relational databases is that when tables are linked together all of the fields within the linked tables are available to the other linked tables. For example, if you create an Invoice table, and link your Customer table using the CustomerID, all of the address fields from the Customer table will now be available to your Invoice table without additional links.
The next step is to determine what datatype you will need for each field.
I have created a list of Microsoft Access Datatypes so you can see all of the datatypes, what each one does, and how they would appear on your forms. One reason that I like Excel for planning is you can use the same spreadsheet from before, and insert columns for your data descriptions. If you want the same description for several fields, it's very easy to copy and paste them. Please see the example below where I have given the descriptions for my previous tables.
I have attached this same Northwind Field Example in a downloadable spreadsheet, so you can use this to rename and modify to plan your database. This version is in Excel 2003, so it should be compatible for most users.
Download Excel Northwind Field Examples
To be continued..
Please contact us for a Free Quote, and see how DB-Guru can improve your operations.