Database Efficiency 1

[jhall01]

This is a very broad question but here goes:

Is it more efficient and better to create tables with more columns and fewer records, or more records and fewer columns?

IE

NAME BusFlag OrgFlag ResFlag
bus1 1 0 0
church1 0 1 0
mr. doe 0 0 1

or

NAME TypeFlag
bus1 B
church1 O
mr. doe R

I know this is a very simplistic example and obvious that the second format is better memory and table speed wise but when you start talking about many columns and thousands upon thousands of records is it better to trim down columns and duplicate records?

name compProduct compAccessory compWarranty
Jon pro2000 printer 2yr
bob home100 camera 1yr
bob PDA

now i know this isn't the greatest but instead have 2 columns:

name product
jon pro2000
jon printer
jon 2yr
bob home100
bob camera...

now databases will be more complex and the 2nd example allows you to have an infinite amount of products ordered for one person but duplicates the name. so my question between the 2 is a table with 4 columns and 3 records faster than a table with 2 columns and 5 records when quering. (of course think in the terms of millions of records or 100k+)

does the amount of columns affect query speeds more than the amount of records in its simplest form? (don't consider indexes and other database features...just columns and rows)

 

[jrbarnett]

Hello

Generally, the "More records and fewer columns" approach will lead to normalised databases, which will have performance advantages and also be easier to maintain. For example, in your first example, if you want to add a fourth flag, you have to add another field to the table, whereas in the second approach you won't.

However, the actual performance differences will depend upon the circumstances in which you are using your database and the queries that run.
I did a degree thesis on this very topic, and timed the query execution using VBA and found that in my test system (using Access v2) the normalised one was by far the fastest option over the slowest, by almost a factor of 10 as well as being the simplest code to write and maintain at the same time.
The fully normalised one had 8000 records in the master table and 2* that in the detail one, and the denormalised ones had the equivalent number of records with the same data.

John

 

[jhall01]

That is what I thought. I have built several applications and tried to normalize as much as possible. There are always certain circumstances which it may not be possible. I always thought it was better to use fewer column and more records, especally if you can index certain columns, like flag golumns and such

Thanks,

Jon H.

 

[tahilg]

Even with normalization you can still end up with large records. Theoretically all 1-to-1 data should be in the same record.

But, for example, people living in the same building will have the same street address. So instead of putting the street address in each person's record you would be better off by putting street addresses is a separate table. You would give each street address a code, e.g. Autonum, and put that code in each person's record instead.

Another reason for breaking out some fields, particularly large text fields, into separate tables is where those fields don't always have something in them. Putting them in a separate table allows you to create records in the second table for only those cases where they do have something in them. Again you can use a code, as above, to link them to the original table records.

A third reason for breaking large normalized records into multiple smaller records is by field usage. If one program uses only some of the fields and another program uses only the other fields then you would be better off splitting the record into two (or more) records in separate tables.

These would save you space and should make your processing faster but would make your programming more complex.

HTH

Gunny