[DISCUSSION] Database schema & coding guidelines
In the wiki page http://wiki.koha-community.org/wiki/DB_schema_bugs, an insconsistency has been noticed: primary keys column name can be: id (in authorised_values table), xxxid (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode), xxxnumber (borrowernumber) => choose how to name PK and update schema accordingly The bug 7065 add a foreign key to the reserves table, so I think it's time to clearly choose how we name primary keys. The possible options are below, my opinion: 1- id alone => will result in complexity & mistakes, this option must be discarded 2- xxxxid => short option, may result in hard readability. 3- xxxxId => we haven't decided if we want UC in field names. I won't discard this option, but does not favour it. 4- xxx_id => _ still undecided in field names, I'm not sure it's much better than the xxxid one 5- xxxxnumber => number is quite long, and may result in very long field names, which is not good. I does not like this option. Let's start the discussion -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
Paul, et. al., On Fri, May 25, 2012 at 12:00 PM, Paul Poulain <paul.poulain@biblibre.com>wrote:
In the wiki page http://wiki.koha-community.org/wiki/DB_schema_bugs, an insconsistency has been noticed: primary keys column name can be: id (in authorised_values table), xxxid (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode), xxxnumber (borrowernumber) => choose how to name PK and update schema accordingly
The bug 7065 add a foreign key to the reserves table, so I think it's time to clearly choose how we name primary keys.
The possible options are below, my opinion:
1- id alone => will result in complexity & mistakes, this option must be discarded 2- xxxxid => short option, may result in hard readability. 3- xxxxId => we haven't decided if we want UC in field names. I won't discard this option, but does not favour it. 4- xxx_id => _ still undecided in field names, I'm not sure it's much better than the xxxid one
I favor this option. I think it's readable, unlike (2), and short, unlike (6). 5- xxxxnumber => number is quite long, and may result in very long
field names, which is not good. I does not like this option.
Regards, Jared -- Jared Camins-Esakov Bibliographer, C & P Bibliography Services, LLC (phone) +1 (917) 727-3445 (e-mail) jcamins@cpbibliography.com (web) http://www.cpbibliography.com/
I also favour "_id" in general... the underscore separates it out as an identifier, and it's already this way in a large percentage of the database. We need to be very careful, though, if we change any of the existing key names, so that it not only doesn't break the Koha code, but also doesn't break saved Reports. The database update script must do a find/replace in the saved_sql.savedsql field (and should also update saved_sql.last_modified in the process). -Ian On Fri, May 25, 2012 at 12:04 PM, Jared Camins-Esakov < jcamins@cpbibliography.com> wrote:
Paul, et. al.,
On Fri, May 25, 2012 at 12:00 PM, Paul Poulain <paul.poulain@biblibre.com>wrote:
In the wiki page http://wiki.koha-community.org/wiki/DB_schema_bugs, an insconsistency has been noticed: primary keys column name can be: id (in authorised_values table), xxxid (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode), xxxnumber (borrowernumber) => choose how to name PK and update schema accordingly
The bug 7065 add a foreign key to the reserves table, so I think it's time to clearly choose how we name primary keys.
The possible options are below, my opinion:
1- id alone => will result in complexity & mistakes, this option must be discarded 2- xxxxid => short option, may result in hard readability. 3- xxxxId => we haven't decided if we want UC in field names. I won't discard this option, but does not favour it. 4- xxx_id => _ still undecided in field names, I'm not sure it's much better than the xxxid one
I favor this option. I think it's readable, unlike (2), and short, unlike (6).
5- xxxxnumber => number is quite long, and may result in very long
field names, which is not good. I does not like this option.
Regards, Jared
-- Jared Camins-Esakov Bibliographer, C & P Bibliography Services, LLC (phone) +1 (917) 727-3445 (e-mail) jcamins@cpbibliography.com (web) http://www.cpbibliography.com/
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Am 25.05.12 18:00, schrieb Paul Poulain:
In the wiki page http://wiki.koha-community.org/wiki/DB_schema_bugs, an insconsistency has been noticed: primary keys column name can be: id (in authorised_values table), xxxid (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode), xxxnumber (borrowernumber) => choose how to name PK and update schema accordingly
Technically, the name of a column does not matter much, since you can always refer to it as <tablename>.<columnname> to make it unambigous. In some databases that support schemas, like PostgreSQL, you can refer to columns even as <schemaname>.<tablename>.<columnname>.
The bug 7065 add a foreign key to the reserves table, so I think it's time to clearly choose how we name primary keys.
The possible options are below, my opinion:
1- id alone => will result in complexity & mistakes, this option must be discarded
I contradict. This results neither in complexity nor in mistakes. Imagine two tables, A and B, which both have an id column, then a select would probably look like the following: select A.id as aid, B.id as bid from A, B, where ... So it's not a problem.
2- xxxxid => short option, may result in hard readability. 3- xxxxId => we haven't decided if we want UC in field names. I won't discard this option, but does not favour it. 4- xxx_id => _ still undecided in field names, I'm not sure it's much better than the xxxid one
These three examples are only a matter of taste whithout any advantages or disadvantages.
5- xxxxnumber => number is quite long, and may result in very long field names, which is not good. I does not like this option.
Let's start the discussion
Since the name of a columns is actually not of any importance, from a technical/database view, why impose restrictions on how to use them? Not the column name, but the table schema should indicate whether something is a primary key or not. That said, I can live with any of above naming schemes, but I would prefer to not have a rule on this. I'd prefer [1] or [4], though.
On Fri, May 25, 2012 at 12:23 PM, Marc Balmer <marc@msys.ch> wrote:
Am 25.05.12 18:00, schrieb Paul Poulain:
In the wiki page http://wiki.koha-community.org/wiki/DB_schema_bugs, an insconsistency has been noticed: primary keys column name can be: id (in authorised_values table), xxxid (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode), xxxnumber (borrowernumber) => choose how to name PK and update schema accordingly
Technically, the name of a column does not matter much, since you can always refer to it as <tablename>.<columnname> to make it unambigous. In some databases that support schemas, like PostgreSQL, you can refer to columns even as <schemaname>.<tablename>.<columnname>.
I agree with Marc here. The best way is the correct way which is to cite the "relative path" ie. table.column. Otherwise I'd prefer option 4 for the reasons already stated by several. Kind Regards, Chris
Le 25/05/2012 18:23, Marc Balmer a écrit :
I contradict. This results neither in complexity nor in mistakes. Imagine two tables, A and B, which both have an id column, then a select would probably look like the following:
select A.id as aid, B.id as bid from A, B, where ...
So it's not a problem.
I contradict your contradiction. If you have branches.id as identifier, then, the FK in borrowers table (the branch of the patron) can't be id, as, in your proposition, the identifier of the borrower will be borrower.id It means instead of : borrowers(borrower_id,surname,firstname,branch_id) / branches(branch_id,name) / SELECT * FROM borrowers LEFT JOIN branches USING(branch_id) and use all field in a hash, your option would result in: borrowers(id,surname,firstname,branch_fk_id) / branches(id,name) / SELECT borrowers.*,branches.id as bid,branches.name LEFT JOIN branches ON (borrowers.branch_fk_id,branches.id), and deal with bid as a hash entry. So, depending on the part of Koha you'll refer to $borrowers->{bid} or $branch->{id} I can't imagine a second that would be easier to read. No gain, big loss, this option must be forgotten according to me. -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
I agree with paul here. I think in the optimal solution would be a table prefix for every column, so we have *zero* name clashes for joins. I doubt we will get there any time soon, but it is something to thing about. It simplifies queries, whereas having id as the pk for each table convolutes and complicates queries. Kyle http://www.kylehall.info ByWater Solutions ( http://bywatersolutions.com ) Meadville Public Library ( http://www.meadvillelibrary.org ) Crawford County Federated Library System ( http://www.ccfls.org ) Mill Run Technology Solutions ( http://millruntech.com ) On Wed, Jun 6, 2012 at 12:24 PM, Paul Poulain <paul.poulain@biblibre.com> wrote:
Le 25/05/2012 18:23, Marc Balmer a écrit :
I contradict. This results neither in complexity nor in mistakes. Imagine two tables, A and B, which both have an id column, then a select would probably look like the following:
select A.id as aid, B.id as bid from A, B, where ...
So it's not a problem.
I contradict your contradiction. If you have branches.id as identifier, then, the FK in borrowers table (the branch of the patron) can't be id, as, in your proposition, the identifier of the borrower will be borrower.id
It means instead of : borrowers(borrower_id,surname,firstname,branch_id) / branches(branch_id,name) / SELECT * FROM borrowers LEFT JOIN branches USING(branch_id) and use all field in a hash, your option would result in:
borrowers(id,surname,firstname,branch_fk_id) / branches(id,name) / SELECT borrowers.*,branches.id as bid,branches.name LEFT JOIN branches ON (borrowers.branch_fk_id,branches.id), and deal with bid as a hash entry.
So, depending on the part of Koha you'll refer to $borrowers->{bid} or $branch->{id}
I can't imagine a second that would be easier to read. No gain, big loss, this option must be forgotten according to me.
-- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08 _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Le 06/06/2012 15:55, Kyle Hall a écrit :
I agree with paul here. I think in the optimal solution would be a table prefix for every column, so we have *zero* name clashes for joins. I doubt we will get there any time soon, but it is something to thing about. It simplifies queries, whereas having id as the pk for each table convolutes and complicates queries.
Kyle, I can't remember where I saw this, but it's an old practise, that is not recommended anymore (SQL generally speaking) Plus I think this is something we can't introduce now without rewriting a lot of things everywhere. OTOH, I think updating primary key names is something more achievable, even if won't be made in 2 clics, I agree. -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
My favorite solution is to use DBix::Class. With the proper mappings only keys should really need to be unique. Eventually, the table classes could in theory hold much of the model code that is in various C4 modules, in an object-oriented manner. Kyle http://www.kylehall.info ByWater Solutions ( http://bywatersolutions.com ) Meadville Public Library ( http://www.meadvillelibrary.org ) Crawford County Federated Library System ( http://www.ccfls.org ) Mill Run Technology Solutions ( http://millruntech.com ) On Wed, Jun 6, 2012 at 4:22 PM, Paul Poulain <paul.poulain@biblibre.com> wrote:
Le 06/06/2012 15:55, Kyle Hall a écrit :
I agree with paul here. I think in the optimal solution would be a table prefix for every column, so we have *zero* name clashes for joins. I doubt we will get there any time soon, but it is something to thing about. It simplifies queries, whereas having id as the pk for each table convolutes and complicates queries.
Kyle,
I can't remember where I saw this, but it's an old practise, that is not recommended anymore (SQL generally speaking)
Plus I think this is something we can't introduce now without rewriting a lot of things everywhere.
OTOH, I think updating primary key names is something more achievable, even if won't be made in 2 clics, I agree.
-- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08 _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Am 06.06.12 21:24, schrieb Kyle Hall:
My favorite solution is to use DBix::Class. With the proper mappings only keys should really need to be unique. Eventually, the table classes could in theory hold much of the model code that is in various C4 modules, in an object-oriented manner.
We have to be careful when introducing ORM-like concepts not to loose beneficial features of the underlying database. More often than not a database abstraction layers lead to a situation where we can only use the common subset of the databases being used, loosing the advanced features a database server might have to offer. And in a complex database based application you most probably want to make use of such features (and yes, without knowing, we do so when using e.g. the MySQL query cache). In practice that means that applications running on multiple databases will have in some cases different code paths for the respective databases. Or using different SQL.
Am 06.06.12 13:24, schrieb Paul Poulain:
Le 25/05/2012 18:23, Marc Balmer a écrit :
I contradict. This results neither in complexity nor in mistakes. Imagine two tables, A and B, which both have an id column, then a select would probably look like the following:
select A.id as aid, B.id as bid from A, B, where ...
So it's not a problem.
I contradict your contradiction. If you have branches.id as identifier, then, the FK in borrowers table (the branch of the patron) can't be id, as, in your proposition, the identifier of the borrower will be borrower.id
It means instead of : borrowers(borrower_id,surname,firstname,branch_id) / branches(branch_id,name) / SELECT * FROM borrowers LEFT JOIN branches USING(branch_id) and use all field in a hash, your option would result in:
borrowers(id,surname,firstname,branch_fk_id) / branches(id,name) / SELECT borrowers.*,branches.id as bid,branches.name LEFT JOIN branches ON (borrowers.branch_fk_id,branches.id), and deal with bid as a hash entry.
So, depending on the part of Koha you'll refer to $borrowers->{bid} or $branch->{id}
I can't imagine a second that would be easier to read. No gain, big loss, this option must be forgotten according to me.
From a technical point of view, there is no need (and no benefit) for
column names to be unique accross a database. The only requirement is that column names be unique within the same table. Other than that, the name is totally irrelevant and can be aliased to anything in a query.
That said, while I still disagree with your reasoning (and you probably with mine), this does not mean that I am opposing to your proposal. I am just saying, that it is not needed, strictly speaking. And I am kind of a database / SQL purist ;)
Le 25/05/2012 18:00, Paul Poulain a écrit : > The bug 7065 add a foreign key to the reserves table, so I think it's > time to clearly choose how we name primary keys. > > The possible options are below, my opinion: > > 1- id alone => will result in complexity & mistakes, this option must > be discarded > 2- xxxxid => short option, may result in hard readability. > 3- xxxxId => we haven't decided if we want UC in field names. I won't > discard this option, but does not favour it. > 4- xxx_id => _ still undecided in field names, I'm not sure it's much > better than the xxxid one > 5- xxxxnumber => number is quite long, and may result in very long > field names, which is not good. I does not like this option. Reviving / concluding this thread: * 4 are for column_id (jared, Ian, Kyle, and me) * 1 is for id (marc_b) * I think chris_n agreed for _id at the end. I consider _id is adopted and will update coding guidelines accordingly -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
participants (6)
-
Chris Nighswonger -
Ian Walls -
Jared Camins-Esakov -
Kyle Hall -
Marc Balmer -
Paul Poulain