Design the tables for a new database

Data, divided into separate tables

Save time and effort with a plan.

For this course, and the other courses in this series, pretend you manage your company's asset data -- computers, desks, and other equipment. You've been using a spreadsheet to enter and manage that data, but the file is becoming so big that it's hard to find and change data, and some of the records are inaccurate.

Moving that data into an Access database can make your job easier, but where do you start? By understanding database design. The language around database design can become fairly technical — you'll hear terms such as normal forms but here are the basics:

Look at the data you need to capture. How much of that data is repeated? For example, how many times does your spreadsheet list suppliers? You look for that repeated data, and you move it into a table all its own. You repeat that process until you've identified the various types of repeated data and moved each type into its own table.

As part of that, you make sure each table contains unique data. For example, a table of asset data won't contain sales information, and a table of payroll data can't contain medical records. The process of breaking your data into smaller tables is called normalization.

After you normalize your data, you then "remarry" it by linking your tables with relationships. The picture shows this. The original spreadsheet places the data in one long list, while the database divides it into tables. In turn, the tables are related together in a way that lets you find information and extract meaning from your data.

That set of tables and relationships is the backbone of any relational database. Without it, you don’t have a database. So keep going, and we’ll show you the design process step by step.