Why not just one big table? 1

[CindiN]

I have always been taught that it is better to make many smaller tables and relate them than it is to make one big table in Access. Is this correct? Why?

Thanks,
CindiN

 

[tlbroadbent]

I think people are referring to proper normalization of a database when they indicate that many small tables are preferred to a large table. Large usually means wide (many columsn) while small means narrow (few columns).

For example, in the relational model it would not be appropriate to carry all of the customer information on every row of the Orders table. You don't want to repeat phone number, address, contact name, etc. on every row in the orders table. In this case you would have two tables, Customers and Orders.

Likewise you wouldn't want to carry all the product informtion on every Orders record nor would you carry all the information about the sales rep who made the sale. You would have Products and Employees tables.

These are simplified examples of the normalization process. There are a number of good articles about Normalzation available online. Check my SQL Articles page for some links to database design and normalization articles.

http://members.home.net/tlbroadbent/sqlarticles.htm

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CindiN]

Thanks TL,

This programmer I am discussing all this with doesn't use Access. And the way he is describing what and how he does his DB modeling, there is no duplication of information. I will have a chance to see his DB this evening, and should have a better understanding of his logic.

Thanks for your input,
CindiN

 

[dschomburg]

A larger table takes longer to query on. If you had a table with someones Name, Adrs, Phone#, Job, Job Adrs, Family history, Car bla..bla..bla
and wanted to query just names in the table Access still Starts with Field1 and "looks at" each field in a record.
Left 2 Right.
So for processing smaller tables are best.
Plus what happens if the table becomes corrupt?

Don't but all your eggs in one basket kinda thing.

 

[ErikZ]

I thought it was size and speed.
If you have duplicate info, then your database will become HUGE and slow.

If you don't have duplicate info, you database can go though several small tables far faster than one larger table.

Do you teach Access or Database design?

 

[tempclerk]

Let us know what you learn of this programmer's database. I'm having difficulty even imagining what kind of data one would need to keep track of that includes "no duplication of information."

 

[CindiN]

For anyone who is interested. I saw this so called one table database, and it's not what was described. It is a different DB Application, and the terminology is different, but the bottom line is that it does have multiple tables. Thanks for your interest.

 

[BNPMike]

You shouldn't be using Access if you don't understand normalisation. Read about that then come back and resume your work.

Normalisation reduces the sensitivity of your application to changes in the data model ie requirements. Also it helps minimse inconsistencies.

As a cardinal rule always start with a Boyce-Codd Normal Form design.

By the way don't use the word 'relate'. You join tables. The word relational comes from the word relation which is a technical term for a table.
mike.stephens@bnpparibas.com