How to Design an Access Relational Database Management System (RDBMS)
By Ben S Beitler
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.
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
