| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Multitable databases.
Expert: Scottgem - 11/3/2009
Question
Hello Sir.
My question is regarding MS Access multitable databases.
Could you please tell me when it is necessary to divide data
into several tables?
Can you tell me some examples?
Please specify, what tables do you suggest to create?
I am interested in distinguishing of concrete entities and
relations among them.
Thank you very much for your soon reply.
With regards,
John
Answer
What you need to do is read up on Normalization. This is a key concept in the design of relational databases that has been in place for more than 40 years. The major purpose of normalization is to reduce redundant data. So the time to split data into multiple tables is when you find yourself repeating data. For example; in an order entry system you may have customer who frequently order from you. You don't want to repeat the customer information with each order so you have a separate customer table and just include a "foreign key" in the Orders table to identify the customer. Likewise, an order might have multiple items in it. So you don't want to repeat the order info for each item purchased. Therefore, you have separate tables for orders and order details.
No one can suggest what tables to create without knowing the purpose and scope of your database. I've developed database with a handful of tables and ones with dozens of tables.
A part of normalization is the concept of entity/attributes. An entity is essentially a table and you use records within that table to define the attributes. The example I use is a hotel with amenities. The hotel is the entity and amenities are attributes of the hotel. Things like a pool, restaurant, room service, WiFi, etc. Inexperienced designers tend to create fields (usually Yes/No) for each attribute. But that would be incorrect. Instead you would use two tables; one as a standardized list of amenities, the other to match amenities to the hotel. Like so:
tblHotelAmenities
HotelAmenityID (PK)
HotelID (FK)
AmnityID (FK)
tluAmenities
AmenityID (PK)
Description
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer Ask a Question
|
|
