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