[Mapbender-dev] Column names

[Karim Malhas]

Hi Christoph,

> I prefer to have column names as less redundant as possible, so  would
> like to have wms.id rather than the current wms.wms_id. It's the same
> with methods and attributes in OOP.

This my opinion as well.

> 
> Currently I'm building a complex metadata form. I have to get data from
> various tables, display them in web forms, and then store the data again
> in the db. I want to make this as easy as possible, with as little
> mapping as possible. This would mean that I should hve a naming
> convention which would allow me to identify the table name as well as
> the column name when updating the db.
> 
> I think this problem is related to what Karim mentioned. What would be
> the alternative? I didn't give it too much thought yet to be honest.


Consider for example:

CREATE TABLE wms {
  id INTEGER,
  name TEXT
}

CREATE TABLE example_a {
  id INTEGER,
  wms_id INTEGER,
  location TEXT,
  FOREIGN KEY wms_id REFERENCES wms (id)
}


CREATE TABLE example_b {
  id INTEGER,
  wms_id INTEGER,
  location TEXT,
  FOREIGN KEY wms_id REFERENCES wms (id)
}


SELECT * FROM wms JOIN example_a ON wms.id = example_a.wms_id JOIN example_b.wms_id;

This will result in an 'ambigous column name "id", "location"' error

There are a couple of solutions to this problem:

A) Prefixing each column name with it's columnname
  
  Output:
  wms_name | example_b_location |  example_a_location

B) Select precisely the columns you need, omitting conflicting column
nmes

SELECT wms.name, example_b.location  FROM wms INNER JOIN example_a ON wms.id = example_a.wms_id  INNER JOIN example_b.wms_id;

  Output:
  name | location

C) Alias where neccessary

SELECT wms.name, b_location example_b.location, a_location example_a.location  FROM wms INNER JOIN example_a ON wms.id = example_a.wms_id INNER JOIN example_b.wms_id;

Output:
name | b_location | a_location

Or perhaps more even more explicit:

SELECT wms.name, 'example_b.location' example_b.location, 'example_a.location' example_a.location  FROM wms INNER JOIN example_a ON wms.id = example_a.wms_id INNER JOIN example_b.wms_id;

name | example_b.location | example_a.location

D) ?


I think B) should always be used insted of  "SELECT *", and I lean
towards aliasing when neccessary instead of prefixing each column name.

Are you familiar on how the 'object-relational' part of postgres deals
with this? I am sure we're not the first ones finding this problem.


Regards,
Karim