There is no recommended way, other than the one you seek to avoid, to get those fancy column names in your results. There is a discouraged way here which could fail with a future release of SQLite.

I've a 'working with databases' book (forget the proper title) which recommends every field in the database has a unique name. This can be accomplished by prefixing the field with a shortened table name, eg, ordId if its an orders table, cliId if its a client, etc. This gets around this issue and does help if you're coding an application - nothing worse than seeing 'name' as a database field - name of what? Or worse, 'value'. Might as well call it 'Bob'.

Thank you

I only note that the approach where column names contain a prefix somehow related to table name has a negative effect, though: if table needs to be renamed, also column names should be renamed to maintain consistency.

Quite right, the approach is discouraged these days as properties always relate to the entity they are in, so the field "make" may seem silly by itself, but inside a "vehicle" table it becomes "vehicle.make" which is no longer silly and indeed much preferred over "vehicle.vehicle_make" or an even more confusing "vehicle.vehMake".

That said, if your goal is to have unique column names across the entire framework, it's a different goal to naming simplicity, and in that case, Chris' suggestion is the only real solution.

The problem here is with the unique column-name goal, not its solution.

In my opinion column names (or property names in any entity) should be unique within the entity, but completely clear in meaning, recognizable throughout, no longer than is needed, but long enough to derive its full meaning easily, both by the current programmer(s) and by any future reader. Further, within any framework or project, all names should have a common format that is strictly adhered to.

This opinion may not be held by all, and I would lie if I claimed I have always adhered to it faithfully, but I find when I do adhere to it, projects are much more accessible/legible to all who work on it, upon design and thereafter.

no longer than is needed, but long enough to derive its full meaning easily,

Does anyone else want to grab a bat and cave in someone's head when they see a field called 'telno' or even 'phoneno' instead of 'telephoneNumber'. I groan at 'membershipno' (they typed out membership, but can't be bothered to type number). Consistency is also key. I maintain (or cry regularly over) a system where we call one particular field a 'gdslocator', but the system we talk to called it a 'recordlocatornumber'. Two different field names ... for the same thing. Why ... just ... argh! This means in other tables we have variants like 'gdsNo' and 'rlNo'. Just ... no. Never. Never do this. Ever.

The origin of this practice (diddling field names to include contractions of the containing entity name) was a side effect of a bunch of early software that used a single namespace for all entity-names (tables, columns, indexes, etc). This meant that you could not have fields with the same name in two different tables/records.

By the mid-1970's all such software had been fixed.

However, like avoiding ethernet auto-configuration because once upon a time some company (that shall remain nameless) really buggered up their implementation, and that companies products were so widely used that it broke auto-configuration world-wide; that issue was also fixed long long ago (by the 1995 if not before).

However, there are classes of people that still insist on doing both of these things, to their own detriment, just because there is an "old wives tale" about some idiot implementation of whatever they are doing. Just like the "old wives tale" about using floating-point for money should not be done, when in fact it is a moron problem: Moron's should not attempt to use floating point (or computers, really) to do things they do not understand.

Agreed, but I usually make an exception for primary keys, preferring something more specific than id for the name of the field.

After all, I find SELECT … FROM foo JOIN bar USING(fooid) easier to read than SELECT … FROM foo JOIN bar ON foo.id=bar.fooid.

I find SELECT … FROM foo JOIN bar USING(fooid) easier to read than SELECT … FROM foo JOIN bar ON foo.id=bar.fooid.

A very good example of different strokes for different folks.

While I absolutely 100% see your reasoning, and cannot fault it, my preference is quite the opposite.

To me,

SELECT foo.id, bar.id, moe.id, ... 
  FROM foo 
  JOIN bar ON bar.foo_id = foo.id
  JOIN chu ON chu.bar_id = bar.id
  JOIN moe ON moe.bar_id = bar.id AND moe.xxx = 1
 WHERE...

reads significantly easier (with less cognitive load) than

SELECT foo.foo_id, bar.bar_id, moe.moe_id, ... 
  FROM foo 
  JOIN bar USING (foo_id)
  JOIN chu USING (bar_id)
  JOIN moe ON moe.bar_id = bar.bar_id AND moe.xxx = 1
 WHERE...

but I'll immediately concede that this is probably more to do with my being used to it/habit than with any demonstrable advantage.

I'd further say, to Chris' point, that both ways, if at least done consistently and adhering to form, would indeed be a pleasure of a project to work on.

I just wanted to say that I appreciate these perspectives that you share here. I'm not always clear when you're being humorous, expressing dissatisfaction, or stating the plain truth of the matter; but I almost always appreciate the broad perspective you provide.

I'm an old man learning new things for what might be referred to as old purposes. So, I've been a bit overwhelmed the past four years trying to figure out in what tools to accomplish my tasks; and I keep coming back to SQLite, Tcl, and C. I'm quite weary of reading how some youngster sandwiched together eight enormous applications, the inner workings of which he has no understanding whatsoever, and developed something "new."