Re: i have table

On 10/4/06, Daryl Richter <daryl(at)eddl(dot)us> wrote:
>
> On 10/4/06 12:20 PM, "Aaron Bono" <postgresql(at)aranya(dot)com> wrote:
>
> > On 10/4/06, Erik Jones <erik(at)myemma(dot)com> wrote:
> >>
> >> Aaron Bono wrote:
> >>> On 10/4/06, *Erik Jones* <erik(at)myemma(dot)com <mailto:erik(at)myemma(dot)com>>
> >>> wrote:
> >>>
> >>> There is one non-SQL related reason that I like to be able to
> order
> >>> columns, at least the way they are displayed whenever the table is
> >>> described: human comprehension. For example, I like to group all
> >>> keys
> >>> in a table before data, that includes primary as well as foreign
> >>> keys.
> >>> So, say I'm building on to an existing application and I need to
> do
> >> an
> >>> ALTER TABLE on an existing table to add a foreign key to an
> existing
> >>> table. I'd like that key to be listed with the other keys, but
> >>> presently that's not possible in a simple way and, to be honest, I
> >>> usually just go without as the process you've described below is
> too
> >>> prone to user (human) error when dealing with live, sensitive data
> >> for
> >>> me to want to mess with it.
> >>>
> >>>
> >>> Ah, but it is possible... if you use views.
> >>>
> >>> I recommend you build views and query off them. Then you can control
> >>> the order the columns will appear.
> >> Which would be great if I didn't have (many) thousands of lines of code
> >> that already use the tables. Besides, this is no where near a 'make or
> >> break' thing. It's just a matter of aesthetic preference.
>
> Ah, but it *is* a "make or break thing." I have seen more than one
> application crash because some developer didn't understand that columns in
> a
> relation (table) have no defined order.
>
> This (along with its sister axiom that rows have no defined order) is one
> of
> the most commonly misunderstood aspects of relational databases.
>
> >
> >
> >
> > So do it as needed and convert your application slowly.
> >
> > I just name my views as table_name_vw so all you have to do is modify
> your
> > queries to hit the _vw instead of just the table. That shouldn't take
> much
> > time to refactor.

I wasn't condoning using select * in your application - if an application is
riddled with that, it is time to start fixing the problem because it WILL
bite you eventually. I never let my team put select * in any queries that
end up in the application code - bad stuff that!

Of course sometimes, especially when you are doing quick throw away queries,
select * is nice and controlling the order is handy. It is also beneficial
when using a generic database tool which will almost definitely do a select
*.

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================