Should we break the single transaction into multiple tables like one main table and other addenda tables with the same primary key to join and fetch the results wherever necessary?
I would say yes. Find a way to logically group sets of columns together and place those groups into separate tables. I'd also be looking for cases where multiple columns really should be multiple rows. This is not uncommon.
David J.
Thank you David.
As you said, to logically break this into multiple tables so i believe it means it should be such that there will be no need to query multiple tables and join them most of the time for fetching the results. It should just fetch the results from one table at any point in time.
But do you also suggest keeping those table pieces related to each other through the same primary key ? Won't there be a problem when we load the data like say for example , in normal scenario the data load will be to one table but when we break it to multiple tables it will happen to all the individual pieces, won't that cause additional burden to the data load?
Also I understand the technical limitation of the max number of columns per table is ~1600. But should you advise to restrict/stop us to some low number long before reaching that limit , such that we will not face any anomalies when we grow in future. And if we should maintain any specific order in the columns from start to end column in the specific table?
