David,
Good database design takes a thorough requirements analysis process. You
should thoroughly examine what information you want to store in your
database, and then start designing your tables on paper. You need to take
into account such things as do you want to have combo boxes for people to
select from, if so, this might require additional tables to define these
lists (I almost never use Value Lists for my Access databases because they
are too hard to edit).
You also need to identify what information is related and how (are they
one-to-one, one-to-many, or many-to-many relationships). As you determine
this information, you will likely move some fields out of one table and into
another. An example of this might be phone numbers. Many newbies to
databases will be inclined to use the spreadsheet method, and just add a
separate field to their Employees data table for Home_Phone, Work_Phone,
Cell_Phone and be satisfied with that, but a more experienced developer might
create an Employee_Phone_Numbers table so that would contain an ID field
(autonumber), an Employee_ID field (long integer Foreign Key to the
Employee_ID PK field in the Employees table), a Phone_Type_ID field (long
integer Foreign key to the Phone_Type_ID PK field in tbl_Phone_Types), and
finally a Phone_Number field. Using this format, you can add types of phones
(Home, Work, Cell, Pager, ...) and if technology results in a new type of
phone, you just have to add it to tbl_Type_Phones to allow the users to fill
this in.
Reality is that most people don't go to this level of detail in the
requirements process, and the final product looks like it. The biggest
problem I have is that when I build databases at work, the people that want
the database are not generally willing to sit down and define their
requirements, they just give you a general "I want a database to do this"
kind of mission statement, and leave it up to me to figure out what they
want. Then, as they start to use the product, want all sorts of bells and
whistles added on. This frequently results in less than optimal database
design.
If you are developing something that only you will be using, then this is
not a big deal, although poor design can complicate the development effort.
If, on the other hand, you are developing your application for multiple users
or for use at the office, fixing bad design decisions can take significant
effort.
Hope this doesn't sound too dismal. The key, in my mind is doing a thorough
requirement analysis up front.
Dale
