Large versus many small tables

[Vittles]

I am in the beginning stages of designing a multi-table access 2003 database.

I will definitely have multiple one to many relationship tables, but I am now debating on whether the main 'application' table should contain all of the one-one relationship fields or if I should make 2 more tables for these.

The main table has the general info for the application, then there is a section for budget (one-one) and a section for special issues (one-one).

Is it better for performance to keep all of this data together or split the other one-one data into separate/smaller linked tables?

This will be a multi-user database.

 

[willir]

Vittles

I will assume you have the fundamentals...

Fundamentals of Relational Database Design by Paul Litwin
Download document from Jeramy's site
Read on-line (HTML) at Rudy's site

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Using relastionships where the related table contains added information is a must. For example, Invoice header and Invoice detail, Family and Family members. Here, you definitely should use different tables.

Using relationships where the related table contains no added value is debateable. For example, you want to rate support calls by importance (urgent, medium and low priority), or indicate the terms of an invoice (30 days, 7 days, immediate). Here, the variable is used for ranking / sorting the main record.

Having a table for this specific type of issue -- you want to have the end user select a specific item (terms on an invoice, urgency on a support call) where the selected item serves no other purpose, then using different tables for each relationship may seem wasteful, may seem to create too much overhead, etc. There must be another way...

You can create a "value list" for the combo or list box used on the form. A common approach, easy to implement. Downside is that you may have some maintenance of adding more items to the list. This can be done automatically through code. Another issue is that what do you do if you accept data from more than one place? You have to duplicate the efforts for one combo box and the other, and may have to ensure the lists are identicle.

My personal approach, and I have only seen this approach referenced in two other locations is to use one table for storing this type of variable - only.

Table format is...

tblGetVar
GetVarType - text, about 15 characters
GetVarCode - text, about 15 characters
GetVarDate - date field was last updated
Description - text, 255 char

Primary key = GetVarType + GetVarCode

Sample data...
[tt]
tblGetVar
GetVarType GetVarCode
InvTerm 30 days
InvTerm 10 days
InvTerm Immediate
CallUrgency 1 Emergency
CallUrgency 2 High Priority
CallUrgency 3 Med Priority
CallUrgency 4 Low Priority
CallUrgency 5 No priority
[/tt]

I query the GetVarType to display the acceptable values for the combo or list box. The query can be run from any form.

I then standardized my approach so that I use a centralized module that displays the (filterd or unfiltered) records, allows / disallows edits, and looks after the maintenance (ie, GetVarType set correctly).

The code is so portable that I copy it from project to project with little effort -- saves oddles of time.

Richard

 

[Vittles]

Yes, I have the basics. My main question stems from whether it is better to have one table with 40 fields (all relating to various parts of a paper application/agency) or to split that table into 3-4 smaller tables and link them using one-one relationships.

The aspect that would influence this is whether Access works faster with one larger table with fewer relationship joins or several small tables with more relationship joins.

Basically for this database there are going to be about 20 tables that will be in a one-many relationship, even without splitting the one large main table into 3 one-one relationships. What is better for performance (either processing speed or to avoid possible corruption)? Thanks!

 

[jsteph]

Vittles,
Access has a max of 255 field per table, so that's something to consider, but in general, I'd avoid the one-to-one situation becaause it greatly adds to the complexity of the application--it's better to keep that data together if it's within reasonable normalization rules.

However, I've found that tables with more than 255 fields are really non-normalized and are crying for a one-many breakup more often than they call for a one-one.

Over a network you might be able to argue that a table with 40 fields will open and display records faster than one with 255, but in my opinion that's not worth the hassle of having, say, a 4-part single table.
--Jim

 

[Vittles]

Thank you - that is what I needed to know.

 

[willir]

Vittles

one-to-one for budget and special interests?

Does this mean you will only store the budget for the current year? By adding a year to the table, you can store historic info.

Not sure about special interests but the plural on the end suggests more than one interest.

I am glad Jim answered your question -- I had misread yoru post.

Richard

 

[Vittles]

This is a single year budget & the special interests section is a combination of different fields - but all just single data entry lines. This database is going to be used for about a year, so the good thing is that I don't have to plan for multiple layers (years, etc.) beyond the ones we already need for that timeframe. Thanks for your response, any info is helpful, if not now, then for future development.