Here are the Five Commandments of database design, whether you use Access 2013 or another database program. A well-designed database makes maintaining your data easier.
Store information where it belongs, not where it appears. Where you store information has nothing to do with where it appears. In a spreadsheet, you type information where you want it to appear when you print the spreadsheet, but databases work differently. In a database, you store information in tables based on the structure of the information. A piece of information may appear in lots of reports, but you store it in only one field in one table.
Store information as it really exists, not as you want it to appear in a specific report. This rule is a corollary to the first rule. If you want book titles to appear in all uppercase (capital) letters in your purchase orders, Access can capitalize the titles for you. Store the book titles with correct capitalization so that you aren’t stuck with having them in all caps on every report. Access has lots of built-in functions that can adjust the way that text, numbers, and dates are formatted.
Avoid garbage in, garbage out (GIGO). If you don’t bother to create a good, sensible design for your database — and if you aren’t careful to enter correct, clean data — your database will end up full of garbage. A well-designed database is easier to maintain than a badly designed one because each piece of information is stored only once, in a clearly named field in a clearly named table, with the proper validation rules in place. Yes, it sounds like a lot of work, but cleaning up a database of 10,000 incorrect records is (pardon the understatement) even more work.
Separate your data from your programs. If you create a database to be shared with or distributed to other people, store all the tables in one database (the back end) and all the other objects in another database (the front end). Then you can link these two databases to make everything work. Separating the tables from everything else streamlines the whole rigmarole of updating queries, forms, reports, and other stuff later without disturbing the data in the tables.
Back up early and often. Okay, this tip isn’t about design, but it’s too important to omit: Make a backup of your database every day. With luck, your office already has a system of regular (probably nightly) backups that includes your database. If not, make a backup copy of your database at regular intervals, and certainly make a backup copy before making any major changes. Keep several backups, not just the most recent one, in case it takes a while to discover a problem.
