[Pdx-pm] (OT) SQL style question

Austin Schutz tex at off.org
Thu Jan 13 14:15:07 PST 2005

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" ?

> 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.


More information about the Pdx-pm-list mailing list