On Thursday, July 4, 2024, Lok P <loknath...@gmail.com> wrote: > > But do you also suggest keeping those table pieces related to each other > through the same primary key ? > > Yes, everyone row produced from the input data “row” should get the same ID associated with it - either as an entire PK or a component of a multi-column PK/unique index.
> > > 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? > Yes, doing this requires additional CPU time to perform the work. I’d say IO is hopefully a wash. > > 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. > In a row-oriented system wider is worser. I start evaluation of table design with that in mind at the fourth column (including the surrogate key that is usually present, and the natural key - ignoring auditing columns.) > > And if we should maintain any specific order in the columns from start to > end column in the specific table? > There is material out there on micro-optimizing column ordering to match with alignment boundaries. I’d the benefit is meaningful but there is a non-trivial cost to actually setup the testing to verify that what you’ve figured out is working. Never actually done it myself. Though it actually seems like something someone could/may have written an algorithm for (though I do not recall ever seeing mention of one.) David J.