| What is a relational database ? A relational database consists of two or more tables whose records are related to each other . The relationship is created by linking a field in one table with a field in another table . Both these fields must be exactly the same datatype . In one of the tables it must be a Primary Key . Key fields are the heart of relational database management . The value in a key field uniquely identifies a particular occurrence of a given record type . For example a customer record might have a key field called Account and a particular customer 's Account field might have a value of 123 to distinguish it from others . Storing copies of the value 123 in order records generated by that customer lets us relate the customers and order records . Only one customer record can have an account number of 123 but many order records can have the value 123 , relating them to the same customer . This is called a one-to-many relationship . The golden rule of relational database design : Never store the same information twice unless there is a very good reason The advantage of a relational database is that there is no more duplication , other than the link fields . The following diagram illustrates this . The Primary Keys for each table are in blue . ( Only a few fields are shown . ) Looking at the customers table , if we want to know more about the rep for each customer , all the data is contained in the Sales Rep table . The sales rep for customer accounts 123 and 124 is AJ and this links to the Primary key AJ in the sales Rep table , so we see his name is Andy Jordan . If we want to know what orders Ronalds has placed , the details can be found in the orders table , linked via the Primary Key Accounts . Relationships in Access Let us see how Access displays relationships . Lets also use an application which you may have had experience of yourself - a library . Here are the relationships in a simple library system . How it works There are two basic tables in a library - the Books table contains all the details of the books in the library . If there a many copies of a book , then only way you can tell them apart is the Accession Number . This is the Primary Key for the table . The second table is the Members table . This contains all the details about the members . There may be two people with the same name etc . so a different membership number is assigned to each member . This is the Primary Key for this table . These two tables become linked , when you take out a book , by means of a third table - the Transaction table . This assigns a separate number to every transaction . This is the Primary Key for this table . When you go to a library all you do is give the librarian your library card ( which has a barcode number on it ) . The librarian scans the card and scans the barcode on the book you are taking out . The two numbers are being linked together in this transaction . The computer assigns the day 's date and the number of the transaction . This completes a new record in the transaction table . If they need to find out your phone number , the link will take them to the Members table and your record . If they need to find out the title of the book you took out , the link will take them to the Books table and the record for that book . Imagine how long it would take if they had to write down your details and the book details ( or even just the name and the title of the book ) every time ! How to set up relationships Firstly , you must plan the tables very carefully . It is essential that each table contains a Primary Key The fields to be related are the same datatype and the same format Once you have created your tables you should set up the relationships before you enter any data . It can be done later , but there are often problems with data not matching etc . If the relationships are created first , the computer will not allow you to enter data that breaks the rules . |