A Relational Database stores data as relations ( a collection of tables with each table consisting of a set of rows and columns. A Relational Database Management System provides relational operators to manipulate the data in tabular form. This article explains step-by-step how to design an Access Relational Database Management System (RDBMS).
Welcome to Bucaro TecHelp!

Bucaro TecHelp
HTTPS Encryption not required because no account numbers or
personal information is ever requested or accepted by this site

About Bucaro TecHelp About BTH User Agreement User Agreement Privacy Policy Privacy Site Map Site Map Contact Bucaro TecHelp Contact RSS News Feeds News Feeds

How to Design an Access Relational Database Management System (RDBMS)

Here's how to get started:

Step 1

On a piece of paper, write down why you are creating a database and how this will be of a benefit to you or your company. What information will you need to store to help manage the eventual reports and general data for your company?

Step 2

Keep data as clean as possible and avoid duplication. Databases are stored in what is known as tables. You can have more than one table which are divided into manageable units of the type of data being held. For example, customer information would be stored in one table and orders in another table which will have a relationship to the customer table. In the customer table, you will have a unique id reference linking to the orders table with the matching customer id (which is the only bit of duplication required!). This will be key to help reduce the need to re-enter customer information each time you placed a new order.

Step 3

With the understanding of tables in an Access database, now make a list of all the tables for each different type of data processes that you will need and that there are smaller and more manageable groups of data. For example, customers will place an order, an order may have several products and each product is stored in the inventory (four tables; Customers, Orders, Order Items and Inventory). Have a separate piece of paper for each table headed up.

Step 4

On each piece of paper (each table), create a list of fields. Fields are specific data information which is held in a table and for the customers table could include id, company name, contact name, address and other related information about the customer. Make sure you have the smallest unit of data required so that later on your reports will be more flexible.

For example, the address field is normally made up around four or five fields together and not stored in one field; street address line 1, street address line 2, city, county and postal code. It is important to have a field which will be a unique reference (and used to link with other tables) so that data can be matched with other known data information (customers to orders with the same customer id field). Complete each piece of paper for each table you have.

Step 5

Now you are ready to build the relationship. This is simply a connection or link between two or more tables. Remember, a customer has a relationship to orders which in turn will have a relationship to order items and each item is related to the inventory table. On each piece of paper identify which field is going to responsible to connect with the other piece of paper and if necessary, add a field so there is a potential relationship (normally the id field).

Step 6

The next step will be to add data (known as records) into the database and check for any errors or duplications. You can use a spreadsheet for now or even build the tables in Access. This is sample data only at this stage and allows you to really test the data integrity and that information is logically stored and easy to manage. Have other members take a look at this to check your results before you start to add data for real (in the hundreds and thousands). You want to fix any errors and integrity issues as soon as possible to avoid the heartache and nightmare of starting all over again.

Conclusion

So there you have it; the first part of planning your relational database. This process is straight forward and surprisingly overlooked by many Access database designers which will save you lots of time.

Notice that I really haven't talked about actually using Microsoft Access at this stage, that's the next step especially if you are new to Access altogether.


Ben Beitler has been building, consulting and training with Microsoft Access databases for over 17 years and is an expert in this field. Being based in London, UK he continually spends his time helping companies design, build and impement Access databases teaching users along the way. Visit his blog: Microsoft Access Database Tutorial to learn more about Access or see his free online user reference at about Access Databases.

RSS Feed RSS Feed

Follow Stephen Bucaro Follow @Stephen Bucaro


Fire HD
[Site User Agreement] [Privacy Policy] [Site map] [Search This Site] [Contact Form]
Copyright©2001-2024 Bucaro TecHelp 13771 N Fountain Hills Blvd Suite 114-248 Fountain Hills, AZ 85268