Column names

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Column names

Karim Malhas

Hi all,

quick question:

Our database columnames follow the schema

  <tablename>.<tablename>_<columname>

which has me typing endless queryies like:
 
  "WHERE gui_layer.gui_layer_wms_id = wms.wms_id"

where I would much rather be typing:

  "WHERE gui_layer.wms_id = wms.id"


// The obvious solution to ease typing would be this:
//
//  "WHERE gui_layer_wms_id = wms_id"
//
// But this makes the column name convention only, instead of actually
// specifying the table-column relationship



Is there an advantage I am not aware of for naming columns this way?

  Karim
_______________________________________________
Mapbender_dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapbender_dev
Reply | Threaded
Open this post in threaded view
|

Re: Column names

Michael Schulz
Hi Karim,

not sure, if there is deeper reasoning behind it (i guess it may be ),
but I try to follow this approach because otherwise you always have to
be careful to not include a column of the name "wms_id" more then
once. I am personally not in favour of aliasing column names when not
really necessary, eg. when you need to output the column name as a
header... otherwise it just shows that the column naming was
ambiguous.

Anyway, you're right that writing those sqls is sometimes awful, otoh
think of an sql where you would just type select * from... and just
because of a join the result contains the same column name twice you'd
have to use .. select a.column1, a.column2 ... also not nice.

Cheers, Michael

2010/4/28 Karim Malhas <[hidden email]>:

>
> Hi all,
>
> quick question:
>
> Our database columnames follow the schema
>
>  <tablename>.<tablename>_<columname>
>
> which has me typing endless queryies like:
>
>  "WHERE gui_layer.gui_layer_wms_id = wms.wms_id"
>
> where I would much rather be typing:
>
>  "WHERE gui_layer.wms_id = wms.id"
>
>
> // The obvious solution to ease typing would be this:
> //
> //  "WHERE gui_layer_wms_id = wms_id"
> //
> // But this makes the column name convention only, instead of actually
> // specifying the table-column relationship
>
>
>
> Is there an advantage I am not aware of for naming columns this way?
>
>  Karim
> _______________________________________________
> Mapbender_dev mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/mapbender_dev
>



--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Aufwind durch Wissen!

Qualifizierte Open Source Schulungen bei der
http://www.foss-academy.eu/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----------------------------------------------------------
Michael Schulz
[hidden email]

in medias res
Gesellschaft für Informationstechnologie mbH

Schwimmbadstraße 2
D-79100  Freiburg i. Br.

Tel:  +49 (0)761 705798-102
Tel:  +49 (0)761 705798-0
Fax: +49 (0)761 705798-09

http://www.webgis.de / http://www.zopecms.de
--------------------------------------------------------------
Geschäftsführer: Stefan Giese, Dr. Christof Lindenbeck
Eingetragen im Handelsregister HRB 5930 beim Amtsgericht Freiburg
_______________________________________________
Mapbender_dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapbender_dev
Reply | Threaded
Open this post in threaded view
|

Re: Column names

Karim Malhas

Hi Michael,

Thank you for your response.
I thought about this more and I drifted off a little, so feel free to
ignore any offtopicness.

>
> not sure, if there is deeper reasoning behind it (i guess it may be ),
> but I try to follow this approach because otherwise you always have to
> be careful to not include a column of the name "wms_id" more then
> once. I am personally not in favour of aliasing column names when not
> really necessary, eg. when you need to output the column name as a
> header... otherwise it just shows that the column naming was
> ambiguous.

It seems to me, that it's usually a good Idea to avoid
statements like

  SELECT * FROM table,

because when dealing with data, it's good to be precise.
And it's actually why I noticed, because I was typing all these
'table.table_column' col_names into my SELECT clauses and thought that things might
be easier if they involved an alias every now and then instead of
specifying the table twice, all the time.

Within the query that's not a problem, because

  SELECT a.*,b.* FROM a JOIN a.id on b.id;

works well.
The columnname conflict only starts on the client side, where a
fetch_assoc returns an array like...

wait, that can't be right ?

given these databases

a:
name|age
--------
foo |12

b:
name | age
----------
bar  | 13


and this statement:

"SELECT a.*,b.* FROM a,b;"

pg_fetch_assoc() returns this array:

  array("name"=>"bar","age"=>"13")

Silently ignoring the 'name' and 'age' columns of the a table.

..."interesting"

I guess specifying the tablename as part of the columnname is a solution
to that problem.

Regards,
Karim
_______________________________________________
Mapbender_dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapbender_dev
Reply | Threaded
Open this post in threaded view
|

Re: Column names

Christoph Baudson-3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

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.

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.

Christoph


Karim Malhas wrote:

> Hi Michael,
>
> Thank you for your response.
> I thought about this more and I drifted off a little, so feel free to
> ignore any offtopicness.
>
>> not sure, if there is deeper reasoning behind it (i guess it may be ),
>> but I try to follow this approach because otherwise you always have to
>> be careful to not include a column of the name "wms_id" more then
>> once. I am personally not in favour of aliasing column names when not
>> really necessary, eg. when you need to output the column name as a
>> header... otherwise it just shows that the column naming was
>> ambiguous.
>
> It seems to me, that it's usually a good Idea to avoid
> statements like
>
>   SELECT * FROM table,
>
> because when dealing with data, it's good to be precise.
> And it's actually why I noticed, because I was typing all these
> 'table.table_column' col_names into my SELECT clauses and thought that things might
> be easier if they involved an alias every now and then instead of
> specifying the table twice, all the time.
>
> Within the query that's not a problem, because
>
>   SELECT a.*,b.* FROM a JOIN a.id on b.id;
>
> works well.
> The columnname conflict only starts on the client side, where a
> fetch_assoc returns an array like...
>
> wait, that can't be right ?
>
> given these databases
>
> a:
> name|age
> --------
> foo |12
>
> b:
> name | age
> ----------
> bar  | 13
>
>
> and this statement:
>
> "SELECT a.*,b.* FROM a,b;"
>
> pg_fetch_assoc() returns this array:
>
>   array("name"=>"bar","age"=>"13")
>
> Silently ignoring the 'name' and 'age' columns of the a table.
>
> ..."interesting"
>
> I guess specifying the tablename as part of the columnname is a solution
> to that problem.
>
> Regards,
> Karim
> _______________________________________________
> Mapbender_dev mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/mapbender_dev
>
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkvdIR0ACgkQGtMIfbycMX5LTgCeLmQezaqSieTrhalpyM94VZcd
AooAni/yMCN9/gWiRlzmpRa542le7ORV
=y64h
-----END PGP SIGNATURE-----
_______________________________________________
Mapbender_dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapbender_dev
Reply | Threaded
Open this post in threaded view
|

Re: 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
_______________________________________________
Mapbender_dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapbender_dev