Re: Design strategy for table with many attributes

On Thursday, July 4, 2024, Lok P <loknath(dot)73(at)gmail(dot)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.