Number of fields in table

[scarlet1978]

Hi,

I currently have a table (my main table) that has more than 100 different fields. I am wondering whether this will become a problem? All of the fields relate to a single record, so I can't see a reason to split the table, but would like to know if this could later lead to problems.

Thanks!

 

[dhookom]

You should be able to add up to 255 fields.

I would generally conside this to be a normalization issue but can't tell since you haven't provided any justification for 100 fields. I rarely find normalized tables with more than about 20 fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

can you tell us about your fields in the table?

Leslie

 

[scarlet1978]

It is a database for insurance information, and ultimately I have three levels, each with a corresponding data table:

1. The account (company name)
2. The policy (can be renewed each year)
3. Addresses attaching to the policy (potentially multiple for a policy).

The policy table is the one with such a large number of fields, and captures everything including name and contact details, type of policy, policy conditions (limits, deductibles etc) and risk quality. All in all this adds up to just over 100 fields which are in a single table.

 

[BNPMike]

I don't think your design is correct. You've split Policy into Policy and addresses but not split off limits and deductibles for example. This may be normalised at this second in time but as soon as some slightly different product emerges you'll have to change your table and associated forms, reports etc.

I am currently working with a car and home insurer and the database has several thousand tables. Three is probably a little too collapsed.

 

[scarlet1978]

I actually have a lot more tables that are used as row input (e.g. type of limits and deductibles etc etc). The policy table is used just to store the information and is made up of IDs, amounts and comments.

If I was going to split the policy table, what would be the reasons for splitting it up?

 

[BNPMike]

The reason is the same as for address - you can imagine there could be more than one of them. So first off -year. Surely you don't repeat all the same information each year?. Then there must be items covered - there must be scope for multiple whatevers - shipments, cars, aircraft, family members etc etc.

Then options that may be added eg for travel insurance you might add personal belongings or legal claims etc etc You don't want to have to go throughout your system adding new fixed fields.