Database Design and Abstract Thought

We're hard at work with a new product to be released very soon, and some interesting things have come up in user testing. One of the most common challenges we see for Microsoft Access users is the database design itself. And by design, I mean the tables and how they relate to one another. When you are working in Access, the form and report design are addictive tools because they are largely what-you-see-is-what-you-get (wysiwyg). But of course, without the underlying tables to store and present the data, your forms and reports aren't going to get you too far.

In the end, database design is an exercise in the abstract. Its taking very concrete concepts like:

Product X can be blue, red, or yellow
Product Y can be orange, gray, white

And then figuring out how to create a database that can help users select from the available options. To do so, you need to move from concrete to the abstract. Otherwise, you get an ugly database where you have a table "tblProduct" with check box fields for each possible color, and bunch of awkward code to make it work. But by thinking of the abstract concepts, which isn't always an easy feat, it's clear you want to actually have a separate table for color. Then, because each product may have more than one possible color, you have to create another table where you can marry them together.

So what's the tip for the day? When thinking of how to architect your database:

1. If you think of the "thing" in the plural, it's probably a table unto itself. Products, customers, contacts, notes, colors, etc. In some cases it's just a simple lookup table.
2. If you are naming the field and you label it in the plural, you may be describing a many-to-many relationship as opposed to just an attribute of the thing your table contains (as in the example we started with where you're tracking Products, if you started to name a field "Possible Colors," it probably deserves some reconsideration as just a field in your Products table).
3. Try to avoid creating separate tables just to differentiate the state of something. For example, if you want to track Customers and Prospects, and all the fields are the same, it's highly recommended that you keep them all in a single table, and have a field that refers to each records' state (customer or prospect). You can then filter your forms, queries, and reports if you need to show only one. Plus, if someone changes from a Prospect to a Customer, it's a single field value that changes. No messy transfer of data from one table to another to signify the change of status.

There are many, many resources to help you design database tables. They all require time to read and practice naturally. But we hope a few of the tips above might help you in your work.

Happy New Year!!!!