One of the fundamental principles of any relational database, like MS Access, is that of the relationships between tables. This does not mean whether the tables have mothers and fathers, sisters and brothers and so on! It refers to the way the tables link together to save you inputting time and storage space.
You can build a database that is based on just one table, but that table could grow at an alarming rate and you would have problems accessing the correct records at speed, as well as having to input the same data over and over again. For instance, imagine you run a small business from home selling beauty products and you have decided to use Access to manage your invoices. In this database you create only one table to hold all invoice the details (customer name and address, invoice number, invoice date, and products details etc). Every time you want to create an invoice you would have to input all of the details from scratch for each of the items purchased on that invoice, as well as details of the customer and details of the invoice itself. This might only be a bit of a nuisance while you have only a small number of customers and a small number of products and you are only producing a few invoices, but once your business grows and you have lots of customers and lots of products you will be spending a lot of time unnecessarily inputting data that you've already entered at least once.
However, if you split your table into two separate tables, one for customers and one for products, you can hold information about these items just once, and that data can be 'referenced' in other tables. You will need to create a field in the customer table that uniquely identifies each customer and one in the product table that uniquely identifies each product. These fields can be allocated in Access as "Primary keys", which means that they are the unique identifiers for just one record in the table. In order to create a record for an invoice you would need a third table (we'll call this table 'Invoice'). The invoice table would need to hold details that are pertinent to just one invoice, like the invoice date, the due date and the total amount of the invoice and it would, of course, need to hold, or link, to information about the customer and the products appearing on the invoice. You could set the invoice number as the primary key for the invoice table and create links to the customer and product tables by including the customer ID (the primary key from the customer table) and the product ID (primary key from the product table). When the primary keys from one table are used to create a link in another table like this they are termed "Foreign keys".
To create an invoice record you would need to enter an invoice number, invoice date and due date plus the client id and the product id. However, the problem now would be that you could only record one product against an invoice as the invoice ID has been used as the primary key for the invoice table and, as we know, the primary key is a unique identifier. You would overcome this problem by creating one more table, this time to hold the product information for a given invoice - we can call this the "Invoice Product Detail" table. For this table you would need to be able to record the invoice ID many times (each time you allocate a product to an invoice) so that data item cannot be set as the primary key and you would need to give the table another field to use as primary key (e.g. Invoice Product ID), and add the Invoice ID as a foreign key. To create an invoice with several products now you would just create the invoice record containing the invoice number, invoice date and due date, add the customer ID (so you can find the customer details to add to the invoice) and add a product ID for each invoice product detail record.
Access is a very powerful and useful tool for small businesses, and its power can be enhanced a great deal by understanding the concept of database relationships.
Jenny Blinman is the managing director of JB Consulting Limited and now trades as Computer Training Solutions in London, Reading, Swindon, Bristol and Birmingham, offering training courses in Access 2003, 2007 and 2010. Call 0800 019 6882 for more details or visit http://www.computertrainingsolutions.co.uk/Access_Training_Courses_Open_Schedule.htm Article Source: |
Tidak ada komentar:
Posting Komentar