Table and Form Design Recommendations

[Guest]

I am looking for advice on table and form design as they relate to database
performance.

I have a Main table with 48 fields. To organize my data better and to
reduce the number of fields in the Main table, I created a Provider table (16
fields) and a Receiver table (16 fields). Their relationships to the Main
table are 1 to 1. There are 500 records in each table. Also, I have a data
entry form with a query record based on these 3 tables. Performance has been
good so far.

There has been a request to add 10 more fields. Performance-wise, would you
recommend simply adding these new fields to the Main table or create another
table with a 1-to-1 relationship?

As for the data entry form, there is another request to add 3 new subforms,
each having a table record source with a 1-to-many relationship to the Main
table. Each subform will display roughly 5 records (3 fields) per record in
the Main table. Performance-wise, would you recommend simply adding these
subforms to my main data entry form or should I have the users click a button
that would open another form which contains these 3 subforms? I’m concerned
about the main form’s load time.

I would greatly appreciate any input.

 

[Duane Hookom]

I think your table(s) need to be normalized. A red flag is when you think
you need to add 10 more fields.

Can you describe your fields and how they are used?

 

[Guest]

The Main table holds essentially contract details. I can place those 10 new
fields into these groups: 1) Nature of Cost and 2) Billing Area. They are
checkbox type fields where the user can check any of these fields, so a combo
box will not do.

As for those 3 subforms, they represent 1) Development costs, 2) One-Time
Costs, and 3) Ongoing Costs. Each contract will have multiple records here,
so I think 3 separate tables with foreign keys are required. I’m just not
sure if I should if I should add these subforms directly in my main contract
data entry form or have the user open another form.

 

[Duane Hookom]

I am even more convinced that your tables aren't normalized. I think you are
storing "data" values as field names.

Can you provide any more information about your fields and how they are
used?

 

[Guest]

I’m confident that the existing tables are normalized. There are actually
quite a few more relationships that I didn’t include since they are not
relevant to this post. However, I would agree with you on the data value as
field names comment for these 10 new fields. I could create 2 combo boxes in
their place, but the user must have the ability to make more than one
selection for each. I just wanted to avoid creating more 1 to many
relationships if I could.

The Development Cost table will have $amounts for different line items
representing different types of development costs. A similar description
applies to the One-Time Cost table. The Ongoing Costs table will have the
$amount for different years. All three tables will reference the contract id.

 

[Duane Hookom]

If you don't want to provide your table structure information, then I don't
think it would make any difference which table you add the fields to. I
would keep in mind that different users might make updates to different
fields. Try to group your fields by "prospective user" so that you don't get
two users attempting to update the same record at the same time.

Properly indexed tables should allow you to use subforms with no performance
issues. If performance becomes an issue, you can modify your design in the
future.

Regarding the normalization... it sounds like you have currency or other
attributes that should be creating records, not fields.