Re: logical column ordering

On 27/02/15 14:08, David Steele wrote:
[...]
> I agree with Jim's comments.  I've generally followed column ordering
> that goes something like:
>
> 1) primary key
> 2) foreign keys
> 3) flags
> 4) other programmatic data fields (type, order, etc.)
> 5) non-programmatic data fields (name, description, etc.)
>
> The immediate practical benefit of this is that users are more likely to
> see fields that they need without scrolling right.  Documentation is
> also clearer because fields tend to go from most to least important
> (left to right, top to bottom).  Also, if you are consistent enough then
> users just *know* where to look.
>
> I wrote a function a while back that reorders columns in tables (it not
> only deals with reordering, but triggers, constraints, indexes, etc.,
> though there are some caveats).  It's painful and not very efficient,
> but easy to use.
>
> Most dimension tables that I've worked with are in the millions of rows
> so reordering is not problem.  With fact tables, I assess on a
> case-by-case basis. It would be nice to not have to do that triage.
>
> The function is attached if anyone is interested.
>
I've never formally written down the order of how I define 
fields^H^H^H^H^H^H columns in a table, but David's list is the same 
order I use.

The only additional ordering I do: is to put fields that are likely to 
be long text fields, at the end of the record.

So I would certainly appreciate my logical ordering to be the natural 
order they appear.


Cheers,
Gavin