Large Variable Data Set

[MikeSmith]

I have a table where it can have from 10 to 150 columns (Fields) depending on the particular record. I am not sure what the best practice is in designing the table.  Is it ok to have a table with so many columns even though there will be many blank fields as the data grows? 

[Ilyrian]

Hi  @MikeSmith

You can have such a number of fields on your table, but it is not a good practice. I think you should try to divide your table into different tables and have a relationship between them.

The following articles may be very useful.
https://www.caspio.com/blog/data-normalization/
https://howto.caspio.com/tables-and-views/relationships/database-relationships/
https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

[MikeSmith]

5 hours ago, Ilyrian said:

Hi  @MikeSmith

You can have such a number of fields on your table, but it is not a good practice. I think you should try to divide your table into different tables and have a relationship between them.

The following articles may be very useful.
https://www.caspio.com/blog/data-normalization/
https://howto.caspio.com/tables-and-views/relationships/database-relationships/
https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

I guess I was too vague in my description of the large dataset. I do have normalized data tables, these large tables are bond amortization tables. Some of the bonds are two year durations, so they may have 8 fields (4 per year for two years), others will be as large as 200 field (50 year duration). So my table may have as many as 200 fields for these numbers. The data is never the same between records. This is why I have the question, is there an efficient way to design such a table or is that the only way?

 

[NiceDuck]

Unless you have a datapage where you will need to edit all of those fields in one go, I suggest separating them into multiple tables. Even with this, I suggest that you use a multi-step form instead.

When you have to display them, join them all together in a view. 

It's a little troublesome to use a very large table. Just finding the right field is already a hassle.