Home

What is a Database?                                                          Software Solutions of Asheville, Inc.

A database is a collection of tables, screens called forms, reports, queries, and programming sometimes called Visual Basic.

The data lives in the tables - think of a table sort of like an Excel Spreadsheet.  There are "fields", for example, Last Name and FirstName and the actual data itself such as "Smith" and "Mary".

Queries sort and present the data.  They get their information from one or more tables.  For example, they might use a Customers table and a Products Table to tell you who bought what.

Screens also called Forms are where you work.  In a well-designed database there is a Main Menu that opens when you start the program.  It contains buttons that open other menus and screens, and runs reports.

Reports are what you use every day - a customer list, product list, sales by product type, and so on.  If you are a non-profit your reports may list constituents and gifts.

Code or visual basic programming sits behind the scenes.  It is written by a programmer and runs when you click a button to open a screen or a report.  This code may loop through a table or products to update prices or send an email to everyone who bought something in the last 6 months.

Put all of this together and you have a database.  How you put it together is another story.




What's a Well-Designed Database?

A well-designed database is one that performs the functions you want and conforms to database rules.

Before you begin writing code look at the reports you want to get. These contain the information that you will need to store in fields in the tables in your database.

If you have paper documents or Excel spreadsheets that you now use this information will need to end up on you database screens or be imported.

Next break down your information into the smallest increment.  Instead of a Name field, use LastName and FirstName.  Instead of an Address field containing the Street Address, City, State, Zip in one field, create a field for each.  That way you can sort on last name, count the number of records by State or City or Zip.  Taking data to its smallest level is called Normalization.

Look for redundant data and put this into a lookup table.  For example, if several employees work for the same employer do not type the Company name into each Employee record as you would do in Excel, but create a Company lookup table with an "autonumber Record ID" and write this Company ID into each person's record in the Employee table.  If the Company name changes, you only have to change it in the lookup table and it is changed everywhere it is used in the database.

When you have your tables built (e.g. Employee table - Company table) go to the database relationships window.  Drag and drop the  Company ID from the Company lookup table onto its corresponding field in the Employee table.  Right-click or double-click the black line and click "Referential Integrity".  This will prevent you from ever creating "orphan records" - for example, Employee records that point to Companies that no longer exist.  Now once a Company if referred to, you cannot delete it from the database.

This is a short overview of setting up a well-designed database.  There are other items such as "limit to list" so that a user cannot make up or misspell a company name, and there are other relationship options.  But if you follow the rules here you are off to a good start.


Home