10 Ways to Mess up your Database

If you would like to receive weekly emails with articles like this one, email Nora

Too many organizations depend on a database designed and created by The Secretary or The Administrative Assistant, without providing that person any training in database design principles. Here is a quick list of 10 items that show how easy it is to design a poor database.

Don't have a field that uniquely identifies a record.
Example: Use a person's name to tell which record is which. How will you tell two Bob Brown's apart?
Solution: Use a unique number or number-letter identifier (such as an Account Number), called a Key Field.
Don't make your fields Atomic.
Example: If you have a field called Name where someone types their whole name, the field isn't atomic. If you have an entry for Sam Adams and another entry for Adam Smith, Adam Smith will come first when the list is sorted because Adam comes before Sam.
Solution: Split the field into more than one field: First and Last.
Allow multiple entries in one field.
Example: If a person has more than one phone number, type all the phone numbers in the same field. This will make it impossible to tell which phone number is a fax or a cell or a work number or a home number, etc. It will also make your reports very messy.
Solution: Break the phone number field into two fields and define which type of phone number goes in the field. If your design needs to allow for more than two phone numbers per person, see #3.
Put everything into one large table.
Example: Have an Invoice table that records all the information about the items purchased and all the information about the person or business making the purchase. This will require a lot of unneded data entry as the same product and person information is entered over and over.
Solution: Break the Invoice table into three tables, Contact, Products, and Invoices. Enter each person and each type of product once. Then have a link from the Invoice table that can share information from the other tables.
When you have a field that needs many entries, make many fields of that type.
Example: Make phone number fields for Work Phone, Home Phone, Cell, Fax, Toll Free, and all the other types of numbers you can think of. This is a problem because your table will become very large and there will be a lot of blanks (Null values) for the people that don't have all those phones.
Solution: Make a whole new table for Phone Numbers with three fields, 1) a link (foreign key) to the person who has the number, 2) the phone number, 3) the type of phone number. This will allow you to make many different types of phone number lists (reports.)
Make fields for values that could be calculated.
Example: Age is something that changes every year. If you have an Age field, someone has to go in every year and change all the numbers.
Solution: Make a DOB field. Wherever you need to report an Age value, use a calculation that will give the Age based on today's date.
Allow NULL values where it makes a record useless.
Example: In the Phone Number table, allow the data entry person to leave the field that holds the phone number empty. That makes the whole record useless.
Solution: Set the field to Required or Not Null (depending on the program.)
Put a field in the wrong table.
Example: Put the price of a product in the Invoices table instead of the Product table. It is easy to think that since the Customer doesn't pay until there is an Invoice, price should be in Invoices. However, each Product you sell has a Price; so, Price belongs to Product, not to Invoice.
Solution: check each field carefully to be sure it's in the correct table.
Put the same information in two places.
Example: Put a field for the price of a product in both the Invoices table and the Product table. This makes it so you have to enter the price twice. That means that, if you forget to do both, your database will have conflicting information
Solution: check each table carefully to be sure that another table doesn't have any of the same fields.
Give your fields non-specific names.
Example: Your database may store several dates, such as the date you bought an inventory item from your distributor and the date the customer bought that same item from you. If you use the word Date for both fields, there could be some confusion when you are using the data.
Solution: use field names that communicate what the significance of the data is. Examples: DatePurchased and DateSold.