Here are some planning steps and guidelines for creating a well-designed database.
Determine the Purpose of Your Database: Write down a list of the reports and information that you want to come out of the database
Determine the Fields You Need: Think about the data type for each field. Will the field contain text information? Numbers? Dates? Write down the data type next to each field.
Determine the Tables You Need: Each table in the database should be based on only one subject. By breaking each subject into its own table, you avoid redundant information and make the database more organized.
Determine the Primary Key: Each record in a table should have a primary key that uniquely identifies it.
Determine the Relationship between Fields: One of the linked fields should be the table's primary key.
Sketch a Diagram of your Database: Draw a box for each of your tables and write the tables’ field names inside that box. Draw a line between the related fields in the tables.
Guidelines for Good Database Design
Each field or column should contain the same type of information
This makes the table more meaningful, more organized, and easier to understand.
Try to break up information as much as possible
This gives you more power to sort, filter, and manipulate the list.
Use multiple tables so that you don’t duplicate information in the same table
Organize your information into several tables—each one containing fields related to a specific subject—rather than one large table containing fields for a wide range of topics.
Don’t use duplicate field names
Duplicate field names can cause problems when entering and sorting information.