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 historical 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 have not talked about actually using Microsoft Access at this stage, that's the next step especially if you are new to Access altogether.



Source by Ben S Beitler

Be the first to comment on "How to Design an Access Relational Database Management System (RDBMS)"

Leave a comment

Your email address will not be published.


*