How many fields should a table have?

[maura]

1. I have a database with 9 tables. My main table has about 100 fields in it,
and I am looking at adding more fields to this main table, and yes they are
all related, but I'm thinking the table is getting too big. Are there
guidlines for a size of a table?

2. The database is split, can I add another table?

 

[John Vinson]

1. I have a database with 9 tables. My main table has about 100 fields in it,
and I am looking at adding more fields to this main table, and yes they are
all related, but I'm thinking the table is getting too big. Are there
guidlines for a size of a table?

2. The database is split, can I add another table?

Albert Einstein was once criticised for the complexity of his theory
of general relativity. He replied in words to the effect "A theory
should be as simple as possible - but no simpler."

A table should have as many fields as are required, and no more.
Sometimes a 60 field table needs more fields; sometimes a 10 field
table has too many. But - in my experience anyway - 50 or 60 fields is
an ENORMOUSLY wide table. If you have 100 fields in your table, that
implies that the Entity represented by the table has 100 distinct,
non-repeating, independent attributes. There may well be entities with
that many but they're very rare.

I would VERY strongly suspect that you're "committing spreadsheet" -
if you have fields with names like Agent1, Agent2, Agent3 or January,
February, March then you're embedding one-to-many relationships within
each row of the table. If you find yourself needing to add more fields
as you get new data, then you CERTAINLY are on the wrong track.

Feel free to post the names of a dozen or so of these fields and
describe their contents if you'ld like some help normalizing - but I
really feel that you need to normalize!

To add a new table to a split application, open the backend database;
add the table; reopen the frontend; use File... Get External Data...
Link to link to it.


John W. Vinson[MVP]

 

[chris.nebinger]

To add to John's post:

I have had times where there were numerous data elements to track, but
often the table includes more types of elements: For example, a
contracts table would hold residential & commerical type contracts, but
each would have different data fields. In one table, or not? Either
way, I say.

But, to let you know, Access only supports 255 fields in a table.


Chris Nebinger

 

[Jeff Boyce]

Further limitations ...

At some point, in one of the versions, Access only allowed a maximum of just
over 2,000 characters in a row. If you had 255 fields (most highly
unusual/unlikely in a well-normalized data structure), you could NOT fill
each of them with 5 characters...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

At some point, in one of the versions, Access only allowed a maximum of just
over 2,000 characters in a row.

Still true.

John W. Vinson[MVP]

 

[maura]

I am utilizing this database to track repossessions. The main table
tblCaseInfo (that I am asing the questions on) has close to 100 fields, here
are what some of the fields are: Account#, DebtorName, DebtorLastName,
DebtorAddress, DebtorCity, DebtorState, DebtorZip, DebtorPhone, DebtorCell,
CoMakerName, CoMakerLastName, CoMakerAddress, CoMakerCity, CoMakerState,
CoMakerZip, CoMakerPhone, CoMakerCell, FieldRep, ClientCode, ClientName,
Leinholder, Year, Make, Model, KeyCode, VIN, Color, Dealership,
ClientNotes... There are also fields for References (Name, Address, City,
State, Zip, Phone); Place of Employment for Debtor (again, Address, City,
State, Zip); and other fields for when a vehicle is picked up (storage and
destination).

The ClientName and Leinholder fields are populated after you put in the
ClientCode from the tblClientInfo. Vehicle Make is a drop down and the info
is pulled from tblMake (this is just a list of car makes).

Any given field that has repeated information (like the client information)
is pulled from another table, but there is a lot of information that is
specific to each repossession; so I don’t think I am "committing
spreadsheet." But I could be wrong.

I am now following the vehicles through the auction and need to add some
more fields, but again these fields will be specific to each debtor/car...
but I am concerned that the tblCaseInfo is getting too large. I think I am
going to do an additional table for the AuctionInfo, but it never hurts to be
knowledgeable about things and any suggestions you can give me I am open to.

Does it seem like I am "commintting spreadsheet?" I have been reading about
Normalizing and I did print off an article Understanding Normalization by
Michael J. Hernandez, so I am trying to critique my own work and see if there
are better ways to do what I am doing. I am open to any suggestions.

PS. I created this database 3 years ago, and it has been working extremely
well for all my offices, but I want to work on making it better! Thank you,
maura

 

[Tom Lake]

Any given field that has repeated information (like the client

information)
is pulled from another table, but there is a lot of information that is
specific to each repossession; so I don't think I am "committing
spreadsheet." But I could be wrong.

I'd create a table of "clients" (which may also be debtors!) which
contains a client ID, name, address, and any other pertinent info on an
individual. The master table would have a case number, client ID, debtor ID
and any other pertinent info on the case.

Tom Lake

 

[Keith Meier, MCSA]

Here's what I would think when designing this DB:

A case has a debtor, a co-maker (which can be null), an asset (the item
being repoed, a financer and an auction/buyer. (5 tables so far)

Tables:
Debtor/Co-maker
- Debtor_ID (PK)
- other debtor info

Asset
- Asset_ID (PK)
- other asset info

Financer
- Financer_ID (PK)
- other info on the financer

Auction_Buyer
- Auction_ID (PK)
- other auction info

Case
- Debtor_ID
- Financer_ID
- Asset_ID
- Auction_ID
(end Tables)

This way you only have to enter information about a particular finance
company, debtor, repo and auction company once.

 

[Tom Lake]

Here's what I would think when designing this DB:

A case has a debtor, a co-maker (which can be null), an asset (the item
being repoed, a financer and an auction/buyer. (5 tables so far)

Tables:
Debtor/Co-maker
- Debtor_ID (PK)
- other debtor info

Asset
- Asset_ID (PK)
- other asset info

Financer
- Financer_ID (PK)
- other info on the financer

Auction_Buyer
- Auction_ID (PK)
- other auction info

Case
- Debtor_ID
- Financer_ID
- Asset_ID
- Auction_ID
(end Tables)

This way you only have to enter information about a particular finance
company, debtor, repo and auction company once.

Why have separate Debtor and Financer tables? The Debtor in one case
could be the Financer in another (and vice versa) and you'd have to keep
duplicate info.

Tom Lake

 

[Keith Meier, MCSA]

Good point and good optimization.

I wanted to show how I broke down a case into its parts and then make the
associated tables.