Designing a Data Base with multiple Tables
It is understandable for us to want to get our Data Base up and running right away. However, as we learned previously, designing a Database before creating it is a fundamental step. A good design is the key to an agile and accurate Data Base. You don't want to be facing problems every time you click on your mouse to search for data. This is why we will design our Data Base as thoroughly as possible with an example.
Imagine we work for a Company; any Company. Every Company has a main goal; selling a product or service. In this case, we will name this product or service "Product". Let's start here.
The information we will need for each sale is:
What have we sold? (Product Name), how much have we sold? (Quantity), who did we sell it to? (Client's Name) and an account number to charge our client with (Client's Account #).
However, as we work with this data, we will also need data on our Client; such as the name of the Company he works for (Company), his/her Last Name (Last Name), the position he/she holds (Position), contact number (Telephone), and shipping or delivery information to send out the "Order" (Address, City, ZIP code)
We might need more information in our Data Base. It might also be a good idea to include how much product we have in stock for each sale. This might also be used to place a new order to our supplier in case we are running out on products. It is evident that if we add more fields to our Table, it will become a headache more than an aide. This is why we need to unify the fields through the criteria we already know.
We will begin with the Table we will use to store the information on our Clients.
Now, we will create a table for the Products.
At this point, we find it necessary to explain the use of some fields. The "Product Code" is the Key field we will need to distinguish some Products from others and relate them to other Tables.
Maybe the need to create a Table for our Suppliers is not evident. Let's study this carefully. Actually, the Product doesn't include only the name. It has a description and it is related to the company which distributes it. It is not enough to say we need a "white dental" Product; we also need to know who can supply it. Thus, we need the data of the Supplier.
However, the first rule is to group our fields in terms of their purpose,. Even if it's evident that the fields in the Product will depend on the Supplier Table, it is also evident that the Supplier Table won't depend on the "Products". A Supplier has many Products. However, we can also use the "Suppliers" table for other options. For example, we can use it to keep in touch with other branches or to develop new lines of business.
Therefore, the solution is to create a field which relates 2 tables together to make the most out of the advantages given to us by Data Base Relationships, and the link between both tables will be the "Supplier ID" field.
Let's complicate things a bit more.
In the "Orders" table we find 3 Code fields: the first one, "Order Code" is the one which belongs to the "Orders" Table. The function of the other 2 is to relate this Table with the others.
Thanks to the "Client's Code", we don't need to include the information of the Client who is making an order in this table. We just need to type in the Code Number and Access will fill in for us; such as the name of the Company, the Address, the Account #, etc...
The same thing happens with the "Product Code" field. Instead of typing in the information of a product (such as its name, price or description) over and over again, Access will relate both tables through this field.
