Which SQL Style is Better?

Ovid on 2005-01-13T21:57:05

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

Quoting column names and using periods as separators:

SELECT "id", 
       "first_name", 
       "last_name", 
       "other_table.id", 
       "other_table.name"
FROM   some_view

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

Am I missing something?

Purdy on 2005-01-13T22:20:16

How come you can't just say:
SELECT id, first_name, last_name, other_table.id, other_table.name FROM some_view

- Jason

Re:Am I missing something?

Ovid on 2005-01-13T22:30:48

You have to quote them because it's invalid syntax if you don't them (unless you actually specify the other table in your "FROM" clause.) You see, the dot is standard syntax for indicating that you're pulling the data from another table:

SELECT name, this.data FROM bar, this WHERE bar.this_id = this.id

Re:Am I missing something?

Ovid on 2005-01-13T22:36:49

Er, "if you don't quote them." :)

double underscores: feh!

lachoy on 2005-01-13T22:22:35

I don't like calling things ugly, but double underscores are ugly. IMO they're subject to the whims of fonts/editors, so it's hard to tell if there's a single or double underscore.

My initial reaction was the same as Jason's -- why do you need the quotes at all?

Re:double underscores: feh!

Ovid on 2005-01-13T22:38:00

See my response above. It's illegal syntax to have a dot in the field name unless you're actually joining on that other table.

Re:double underscores: feh!

jplindstrom on 2005-01-13T23:19:00
If two underscores are considred ugly, is there any reason you can't just type:
SELECT id, first_name, last_name, other_table_id, other_table_name FROM some_view
It's probably pretty obvious that the prefix actually is a table name prefix. At least that's my experience when naming constraints in our databases. They follow that naming convention.

Ovid, if you can post it, what would real life examples look like?

Better is relative...

jarich on 2005-01-13T23:22:05

It took me a bit to realise how it was that you had a choice in the matter. Like Purdy I just wondered why you didn't use un-quoted (normal) SQL.

But of course you're using a view and you're talking about how to name columns in that view in a meaningful way. It's clear once I made that minor mental twist.

In my opinion don't use the latter. Anything that suggests you can use dots, as you do with normal selects from tables, will probably have people making the mistake of deleting or omitting the quotes and wondering why it doesn't work.

If that leaves only double underscore then I guess you'll use that. lachoy said something about fonts and editors which should be considered, but most editors I use for code make two underscores look like two underscores, or one very long one, which is much the same thing.

- jarich

Re:Better is relative...

vsergu on 2005-01-13T23:31:52
I agree about not using dots. If you're going to use weird characters in the column names that require quoting, then why use dots, which will only confuse things because of their normal use? You'd be better off using % or $ or / or anything other than dots.

But really, rather than weird characters or double underscores, if you have to have something why not a prefix like 'NP_' for 'nonprimary' or something? Or maybe you can distinguish by using uppercase?

Re:Better is relative...

Ovid on 2005-01-13T23:41:38

I had thought about uppercase (studly caps) too, but then Theory reminded me that most databases are case-insensitive. Not only is the case not meaningful, it's not guaranteed to be displayed in any consistent way.

Re:Better is relative...

Aristotle on 2005-01-14T08:57:11
Yeah. I agree with jarich's points about not using dots too. Personally I'd suggest the colon as a separator.

Don't do it

VSarkiss on 2005-01-14T01:16:15

Seriously, don't use either approach.

What's not clear to me is why you're using the views in the first place. If you're trying to hide or limit access to the underlying tables, then either approach goes counter to the goal. If you're using the views to encapsulate logic or something like that, then choose meaningful names for the columns. There must be some reason why that column is visible in the view -- choose a name that describes its function.

In any case, letting the underlying tables "show thru" is not good data modeling practice, in my opinion.

Re:Don't do it

Ovid on 2005-01-14T01:46:55

While this is not my design, in this particular case it works, though it's a long story why. The database, while still being used as a database, is being used in a most unusual fashion. We are not trying to hide or limit access and, because of the way things are structured, these names really are meaningful. It's a long story why but when Bricolage 2 comes out, it will be more clear what is going on.