Recently I had to replace an application that was not designed for growth. As with most the applications I’ve seen written in classic asp with an access backend, the major problem is with the database design. The difference between a bad design and great design is all in the tables.
Most databases revolve around a client/customer. This is where the average poorly designed database goes wrong first. Countless of databases I’ve seen have the name data stored in the same table as the address and phone data. Some even have other info all in this same table. This design does not allow for growth. A properly designed name table consists of an ID field, a full-name field, a name type field (meaning business or Individual). The ID field should not be an auto-generated integer. Instead of auto-generated you should have a table used to store the ID numbers of all your tables. So the first place you should really start is creating a table named DB_Counters. This DB_Counter table is one row of data with each column holding a different ID total (NameID, PhoneID, AddressID). I usually name column 1 ID and name my next columns for each table.
Back to the root of all databases, the name; you might ask why just an ID, FullName and NameType columns. In a single word growth. Customers come as either individuals or businesses. They can have multiple addresses, multiple phone numbers, multiple orders, multiple employees, family members, prospects, the list goes one… This single table can fill drop downs, can be used to search for names, you can search by name type, or all names.
Tables 2 and 3 should be Individuals and Businesses; the ID columns of these tables should use the identical ID integer that is used in the NameEntity table. In the Individuals table you should have a break down of the name, meaning first, middle, last, suffix, prefix, professional title, etc.. The Business table can house, taxid, and other business information like that. The next 4 tables you should create are Phone, PhoneLink, Address and AddressLink. The phone table should have an ID, Phone Type, Phone number and extension columns. The Phone link table is just 2 columns the NameID and PhoneID . This design allows the name entities to have a multitude of phone numbers. Follow this same design with the address table. Then add another column to the Individual and Business table to indicate the primary addressed.
Other tables you should consider are one that could hold dropdown list data and/or checkbox, labels, etc.
If you follow this design style with all the tables, you won’t have to code around a poorly designed database, instead you will code for growth. As the business object changes, your database will be able to handle those changes.