Help with thinking about tables and forms

[bytreeide]

From what I understand, it is bad practice to have a TABLE with too many FIELDs (40+). I was wondering if there were situations that were exceptions to this practice? In my case I have 7 TABLEs, all of which are being incorporated on a single FORM. All of the records in the TABLEs are relate to one and only one record in the FORM (relationship is set to "one-to-many" because I have ID fields (TYPE: NUMBER) on a master TABLE and there are 6 other table that all have ID fields (TYPE: AUTONUMBER) that are related to thier specific field in the master TABLE). To reinstate there isn't a case where I would choose to duplicate the use of the record from any of the six "related" TABLES.

NOTE: I want to be able to input data into all of these seven tables from the same form.

I was wondering if this would be a case where all of this data could be merged together to make one big table and then it would work like I am imagining it.

Or am I just not thinking about this in the right way. I could use some suggestions to point me in the right direction.

I have tried using Subforms to accomplish this, but initial attempts did not yield the desired result, so other methods were sought.

Would it be appropriate to change the relationship state so that all of these table have a One-to-One relationship?

Do I need to have separate forms to enter in information for all of these other tables? If that is the case then there is no point to having them all on the same form. I just don't want to create a lot of forms. There has to be a simpler way to go about this than I am thinking about.

Your suggestions are greatly appreciated.

[ranman256]

If we could see your fields, that might help us.

[orange]

The number of tables in a database application is not arbitrary. Working from a clear description of the business involved, you can identify the subjects(entities) required. As you add more details to the description, you will get information suggesting how these entities are related(business rules/facts). With some "test data" and some testing scenarios, you can validate/confirm the entities, attributes and relationships will or will not support your business facts. If the model does'nt support the business facts, identify what is wrong --the data, the business fact.... then adjust as necessary and retest/redesign until it does support the business. Then develop the database using the model as a blueprint. see this for more info

As for the 40+ fields in a table, typically tables in database are narrow(fewer fields) and deep(more records).
But there can be exceptions. It depends on the application, but generally (not always) tables have fewer than 40 fields.

Work through this tutorial for identifying tables, attributes and relationships (30-60 minutes).

Get your tables designed to support your business before getting involved with forms.

Good luck.