[RESOLVED] Easy question regarding Access database.

[soutie]

hi all,
This might be a silly question but is there an advantage of creating many tables over just one. Eg, one table with 20columns as opposed to two tables with 10 columns? Is the advantage just for manageability or is there a difference in speed? I'm new to programming so i was just wondering if it will affect performance drastically if i create an app with access and vb6 frontend with a single table with about 100 fields/columns to store name,surname,telephone etc...

[sparbag]

Hello Soutie,

The main reason that a database has many tables is because the data has been relationally modelled. This is the process of creating tables to hold repeated information and therefore minimise data repetition.
For an address book you could quite easily use a single table and be very happy carrying out very quick searches against the data as searching a single large table is always faster than searching many small tables.

You could produce a table of Cities, a table of Towns and a table of Streets which would minimise your data entry whenever there was repeated entries.
Personally I think that 100 columns is too much for a single table mainly because each record would contain NULL values for fields. When you have excessive NULL values in a table it is a sign that the table requires normalising.

Kind regards

Steve

[soutie]

howzit steve, much appreciated mate. It clears a lot up. Thanks again.

[soutie]

i should mention, 'howzit' is a southafrican slang term for 'whatsup' or the like

[si_the_geek]

I recommend taking a look at the "Normalisation" article in the design section of our Database Development FAQs/Tutorials (at the top of this forum), because it covers this topic in a bit more detail.

The design of your table(s) depends on your situation, but as a general guideline more tables (up to a point) means more efficiency, in terms of database size, manageability, reduction of errors, and usually speed of queries too.

The downside of more tables is extra work in your program etc, so you don't really want to add more than you actually need. sparbag's example of an address book is one where a small amount of tables (perhaps just one) is actually better because there is unlikely to be repetition, but that kind of situation is rare.

[soutie]

thanks.