You may well be familiar with the term 'Relational Database' already, but perhaps don't understand what it means. If that is the case, then, hopefully, this should put you on the right track to penetrating the meaning of the term. Relational Database design is all about how database tables are connected with each other. Whilst the novice database designer will create one single table with lots of fields, the relational database designer will look at the the data that needs to be stored and will put it together in a number of groups. Each group is then made into a separate table. But because the subjects of the data stored in separate tables are connected in the real world - for example customers and orders - we need to create a relationship between the tables within the database itself.
Lets take a look at how we go about doing this in practice. We will use a simplified example of a customer/order database.
Imagine we have a number of customers who order electronic products by email, and we want to create a database to record both the customer's details and their orders. As we mentioned in the example above, we would go about this by separating the customer details and orders into two separate groups which we then make into tables. We do this for the simple reason that if a given customer makes more than one order, we only have to enter the customer details once in the customer table. Because we are going to create a relationship between the customer table and the orders table, all orders for individual customers will be connected with the correct persons' customer details. This is made possible by connecting the primary key field of the customer table with the corresponding foreign key field in the orders table. If you remember, Primary and Foreign Keys were covered in my last blog post.
- The first step is to create a new Access Database. Lets call it Customer Orders.
- Next create a new table called tblCustomers with the following fields - ID (autonumber, primary field), FIRSTNAME (text), SURNAME (text), ADDRESS1 (text), CITY (text), and POSTCODE (text).
- Create a new table called tblOrders with the following fields - ORDERID (autonumber, primary field), ITEMORDERED (text), DATE (date), PRICE (number, set the format property to currency), CUSTOMERID (number). CustomerId is going to be the foreign field when we create the relationship.
- To create the relationship, select the DATABASE TOOLS RIBON and click the RELATIONSHIPS icon (from the SHOW/HIDE group).
- This opens the RELATIONSHIPS window. You will also see the pop up SHOW TABLE form the first time you open the window. We are now going to select which tables are going to be used in the relationship.
- There are just two tables in our example database. Click on tblCustomer to highlight it in blue if it is not highlighted already. Then click add. Do the same for tblOrder, then close the pop up SHOW TABLE form.You should now see the two tables represented as separate box's in the RELATIONSHIPS window. You will notice in the diagram below that each box has it's fields listed and primary keys indicated.
- We will now click the ID field in tblCustomer and drag it to the CUSTOMERID field in tblOrder. Notice the mouse pointer image changes from a circle with a line across to a plus sign as it hovers over the fields in tblOrder. When you release the mouse button at the end of the click and drag operation, a new EDIT RELATIONSHIPS pop up form opens.
- Click the three tick boxes which say ENFORCE REFERENTIAL INTEGRITY, CASCADE UPDATE RELATED FIELDS, and CASCADE DELETE RELATED RECORDS. We won't be going into what these do in this blog post.
- Click the CREATE BUTTON. Our table relationship is now in place. This is what the database window looks like now. Note the line drawn by Access between the ID field of tblCustomer and the CUSTOMERID field of tblOrder.
All we need to do now is enter some data and test out the relationship. Try entering this data from the image below into tblCustomer: