I have a situation where I need to store common fields for different areas, as well as fields that are only used by distinct areas.

I am using Access 97, and I was wondering if it is better to have one enormous table with all possible fields, or to put all of the common fields in one table with an id field, and then a separate table for each of the areas with unique fields with a one-to-one relationship to the common table. I have tried a few scenarios and I can't seem to figure out if using one table for all fields would have a significant impact on the size of the database. It doesn't seem to increase the size of the database just by the existence of more fields in a table unless the fields contain data.