One good way to design a database is to draw it out before you begin writing it. You drawing should show the different tables in your database, the different keys, and how the tables relate to each other.

Draw your tables

I like to use pictures to draw my database. Draw squares to represent tables and write the fields in the tables. Make a note of what type of data each field will contain.

Remember these basic tips.

Generally, store numbers as a string.

Unless you are going to do math on a number, store it as a string. So, phone numbers should be stored as strings. (Strings are a type of data that can contain letters, numbers,and special characters.)

Make a separate table for defined acceptable values.

If there's going to be a standard set of values for a field, make a separate table of those values and have answers selected from that. Avoid free form answers except for legitimate free form areas. For example, if you have a field called "Where did you hear from us," make a separate table with acceptable answers like Google, Yahoo, radio ad, friend, and other. When you eventually design a form, this field will be a drop down menu and it can be selected from the table of acceptable values. Why would you do this? It will be easier to sort data and run reports later, because the data is consistent.

Primary keys need to be something completely unique.

Sometimes you can combine two fields to make a primary key, like last name and zip code, but it's hard to guarantee that those are unique. Phone number is a good unique field. SSN is not, since there are duplicates of that and no one likes to use their SSN anyway. Phone numbers are totally unique. The other way to make a primary key is to have the database generate it in an automatic numbering scheme.

Choose your software

Once you do that, then building it is just matter of choosing your software and off you go. Do you need access to it everywhere? If so, use an online database—and there are boatloads of them. Or, will it suffice to just have on one computer and you can build it in Access or even Excel.

Here's a picture of a fake database if you're interested in one way to draw them.. (There are official names and symbols and stuff to do this—I didn't do it the "right" way, because, well, I like colors.) On the picture, whenever a field has an arrow that takes it to another table, that will end up being a drop down menu on the form you generate to populate the databas

Share