single table vs. many with one-to-one relationship

[number_one]

I have a situation where I need to store common fields for different areas, as well as fields that are only used by distinct areas.

I am using Access 97, and I was wondering if it is better to have one enormous table with all possible fields, or to put all of the common fields in one table with an id field, and then a separate table for each of the areas with unique fields with a one-to-one relationship to the common table. I have tried a few scenarios and I can't seem to figure out if using one table for all fields would have a significant impact on the size of the database. It doesn't seem to increase the size of the database just by the existence of more fields in a table unless the fields contain data.

[Cakkie]

You should place all repeating data in seperate tables. This will save space, and spee dup things. This is called normalization, wich should (almost) always be applied to a database. There are 4 step for normalization. It all comes down to place all data that is repeated in a table in another table, removing comuted colums etc. Of course, this is not always what you want.

Common thing that are placed in another table are things like country, state and that sort of things.
eg

Employees
---------
Name
Address
Country

should be

Employees
---------
Name Countries
Address ---------
CountryID ------- CountryID
CountryName




Because the country is entered only once in the countries table and linked via the ID, the employees table will be smaller, because it will only an ID, which ins most cases is and integer or a long, which is still way smaller than a string field of size 20

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Iouri]

I absolutely agree with Cakkie. It is much better to use multiple tables with relationships between them. Tables must be normalized. It will make your life easier later when you need to maintain the table.

Iouri Boutchkine
iouri@hotsheet.com

[number_one]

My question was not understood. I completely understand database normalization and I practice it. The situation I was talking about was a one-to-one relationship vs a single table in terms of the size of the database.

In other words, I have a main order table with say, order ID, order date, and status. There are 8 different kinds of orders that can be entered, and each of these different kinds of orders requires different fields in addition to the three common fields that are listed above. My question was: Is it better to put ALL of the possible fields for each of these different types of orders all in the same table, or is it better to have a main order table that holds those three common fields listed above, and then a separate table for each of the different kinds of orders that links with a ONE-TO-ONE relationship to the main table.

eg.

Orders
---------
[Order ID]
[Order date]
[Status]
[Type One Special Field]
[Type Two Special Field]
...

or

Orders
---------
[Order ID]
[Order date]
[Status]

Type One Table
--------------
[Order ID]
[Type One Special Field]
...

Type Two Table
--------------
[Order ID]
[Type Two Special Field]
...




This has nothing to do with normalization because it is not about duplicate data or about problems with deleting or updating data. I simply wanted to know if it would take more space to put all of those fields in one table, even if only some of the fields would be used in any particular order.

[Cakkie]

Well, I wouldn't worry about the size, going for more tables will increase the database size, but the size difference can be ignored. The thing that does make a difference is speed, something we al want.

What to choose? it depends...

First of all the type of the fields depends, if it are numbers, leave them in, end of discussion.

If there string, what are they ment for?
If you need to do a lot of searching, it might be worth seperating them cause searching a large table can be slow. Of course, this will involve a little more overhead when combining them, cause it would involve joins.
When searching multiple fields, you can't go besides joins (unless you want to search each table seperately, which, you will agree with me, you don't want to do). On the other hand, searching the other table (the one you took the fields away from) couldn't get any faster.

Another alternative, wich lies between the two others, is seperating them with a 1-to-1 relationship, but keeping the three fields together in one table. this is a good solution if you don't need to do a lot of searches on the three fields, but a lot on the others. Also, this decreases the overhead needed to join the tables, cause you only need to join 2 tables in stead of 4.

Of course, as said before, it all depends on what you want at what cost.
It's all a matter of what you need, at what cost.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook