MS Access - better to have more tables or more columns?

[pawelch]

Hi all,

I think I have got a general questions - however, I prefer to ask experts rather than base my design on vague opinions. So, I want to design a simple database for contacts of a company. Of course the design and the company structure and activity requires some tables. However, I have a question regarding "small" tables with a primary key and one column only. Let's name them "type tables". I have a few of these tables such as:

JobTitle:
|JobTitleId||JobTitleType|

or

PersonType:
|PersonTypeId||PersonType|

Is it a good practice ? Or it is better to drop those tables and include them to the person table? I am aware that afterwards I might use them as a base for scroll list and whatever changes in a "type table" then it changes in front-end design instantly. Nevertheless, I would like to know your opinion.

Also my second question is (I know I should not ask two but it is so related so I prefer to add it here without utterly disobedience of post rules) I have a table AddressDetails that stores data for companies and clients. So that the clients and companies tables have got reference to the address table. Is that a good approach? I am asking because I used to help someone with database and when the database was huge it took a long time to load data with multiple SQL queries. Thus, maybe it is better to extend client and company database with address details columns ? Therefore, we limit ourself to loading data from one table only. I would like to know your opinion about it.

So in general, I would like to know if in access it is better to store less tables but waste memory, or it is better to have many tables and cause extra processor overload. This is my biggest concern right now.

Also, where could I find more info abut this matter as I do not know how to ask google ?

thank you all for your answers and suggestions.

Cheers!

 

[Galaxiom]

I would say neither. It is better to have more records. I could say I was just trying to be difficult but there is some truth in it.

But seriously. If the relationship is one to one then put them as fields in the same table. If there are multiple addresses they should be in a related table. Just one, then put it in the table.

Either way should be fast. Your colleague's database probably had other issues that made it slow.

Also remember that while some principles are important, to some extent database design is an art. Some question are like asking what colour to decorate a room.

 

[DCrake]

In a nutshell Columns are expensive whereas rows are cheap.

As far as using a lot of table for 2 field lookups there are varying opinions, I would not create a table that contained

Y - Yes
N - No

or

M - Male
F - Female

But if the list was list of categories that could intime increase/decrease then yes use them.

You could always consolidate the lists into one table.

Id
Description
Category

N - No - Logical
Y - Yes - Logical
M - Male - Gender
F - Female - Gender
B - Black - Color
W - White - Color
G - Green - Color

etc


Then when you want to refer to a lookup list you select them and filter by category.

 

[gemma-the-husky]

if you have a lookup table of permitted values, you limit the user to those values - which prevents bad data being caused by typing and punctuation errors. You can easily provide facilities to let users edit the permitted values list.

it also looks better - as it gives users a drop down to select from - eg with titles, selecting form a list

Mr
Mrs
Miss
Ms
Prof
Dr

etc etc, is generally better and cleaner then letting them type in whatever they want.

Assuming you add a numerical index to the lookup field, and you store the index value, rather than the text - it is also more efficient, and enables you to change the text, without needing cascading updates.


---with regard to addresses, i tend to have separate tables for each type of data table - but a lot of MVPs especially seem to have a single address table, with a tag to identify its application - eg employees, customers, suppliers - on the basis that you then only have 1 maintenance form, and also that some addresses fall into multiple categories.

 

[Rabbie]

The rule of thumb I was taught was Access tables should be long and thin rather than sort and fat.

But the important thing is to make sure you understand Data Normalization.

 

[pawelch]

Dear All
thank you for your response I found them really useful.
My biggest concern is the efficiency of MS Access. I used to design simple databases for MySQL and it worked pretty fast even with multiple records. And as I was saying - I observed some decreased and slow performance of the MS Access database that was installed on a stand-alone windows system with a back-end structure. The database was badly designed and it was far from normalised structure in any sense. With 500k entries all together in multiple tables, running some forms (that used double and triple joins) required around 10sec what was too much for that company (call centre). Moreover, with that amount of data, the re-design could not help, because all the columns were needed in some sense and made up for artificial sorting keys. So, maybe the badly designed database was the reason for slow performance. On the other hand, maybe it is the case that for large databases, say more than two hundred thousands entries, we should not use MS Access.

Anyway, GalaxiomAtHome and Rabbie pointed out that in general it is better to have more records than many columns so I will take it as well as a rule of design.

the lookup tables make perfect sense to me and obviously I am not going to store them for boolean variables

Once more time - thank you for your suggestions!!

 

[gemma-the-husky]

a large number of rows shouldnt matter to access, as long as data can be retrieved based on indexes.

what COULD cause slow performance in a linked access database is not having a permanent connection to the backend. In those cases access has to keep re-opening the backend, which can lead to a performance lag, as windows needs to check this is permitted - especially in a multi-user situation.

 

[Galaxiom]

200K records is not a big database by any means and certainly not a problem for Access. I have an Access database with more than nine million records in one table. That table has about a dozen fields and I don't have speed problems.

The most common problems with speed (other than network problems mentioned by Dave).

Retrieving more records than required. Limit the range of records returned using the where clause of the recordsource query. This range can be extended by the user selecting a different range.

One trick with large tabbed forms is to not set the RecordSource property of other tabs on the form until that that tab is opened. It delays the tab opening but that is better than waiting several times a long for the whole form to load.

Failure to index fields used in Joins or Where Clauses.

Using long text stings as key fields. Far better to represent the value as an integer or a single alpha character.

Using DLookups to populate multiple controls.

 

[pawelch]

what COULD cause slow performance in a linked access database is not having a permanent connection to the backend.

I am sorry for asking this silly question, but do you actually mean to set up a physical connection to a database with code ? In other words, to set up a startup procedure at front-end applications something like:
CODE] Dim db As Database
Set db = CurrentDb()[/CODE]
and the operate on this connections afterwards; till the moment when the FE is closed and the connection released? Or you are writing here about having some tick box clicked in MS Access options or something ?

thank you for your answer.


also

One trick with large tabbed forms is to not set the RecordSource property of other tabs on the form until that that tab is opened. It delays the tab opening but that is better than waiting several times a long for the whole form to load.

this is always very tempting to have one form with multiple SELECT queries. In terms of performance, does it make any difference if you perform SELECT in RecordSource or via VBA commands?


Thank you for your help.