[Pdx-pm] (OT) SQL style question

[Austin Schutz]

On Thu, Jan 13, 2005 at 02:02:06PM -0800, Ovid wrote:
> Hi all,
> 
> Sorry for the OT question (well, it's not completely OT as this impacts
> how Bricolage 2.0 will be implemented.)
> 
> We have views that reference more than one table.  Our views have a
> primary table they are based upon and fields from "non-primary tables."
>  To make it very clear which fields are which, there's been a bit of
> discussion regarding how to make it visually distinct that a particular
> field is from a table that is not the primary table of the view.  Which
> of the following is clearer?
> 
> Using double underscores to separate the other table name from its
> field name:
> 
>   SELECT id, 
>          first_name, 
>          last_name, 
>          other_table__id, 
>          other_table__name
>   FROM   some_view
> 

	Seems quite clear to me.

> Quoting column names and using periods as separators:
> 
>   SELECT "id", 
>          "first_name", 
>          "last_name", 
>          "other_table.id", 
>          "other_table.name"
>   FROM   some_view
> 

	This scares me. I'm not sure if it's portable, but it will
_definitely_ bite you when someone forgets the "s. I've never even
seen this done before.. what happens when you join something with the
view and need to specify a column? SELECT some_view."other_table.id" ?
Scary.

> Is the latter portable?  Is the former too confusing?  Which is easier
> to read?  Are there reasonable alternatives?

	I don't know of any alternatives, but I'd be interested to know
if there were.

	Austin