Well I apologize if I seem to have wandered a bit in my postings here but I have enjoyed the exchange and it helped me think through the problem a bit. I started out with an observation about the "id" field being the primary key and some thoughts on its dangers. Then I wandered a bit. But I do have a point: the DAL does not seem to support multi-column keys. It also does not support multi-column constraints.
I'm curious why. Is this something that's just not done yet or is it something for which there has been no need or is it something the developers don't want to do for some reason? The answer of "don't use the DAL" is kinda missing the point. I ***want*** to use the DAL. I would bet that there is some not so simplistic/short web2py code supporting the DAL. Further, I ***can*** just add a 'orderby' clause to the select() function but this is expensive. Correct me if I'm wrong but I believe that a table's primary key is automatically indexed. When selecting on the primary key the db automatically uses its index - which speeds things up quite a bit. Adding indices to much-used queries can really speed things up. So if I cannot use indices in the DAL ***and*** I cannot have my primary key indexed then _sigh_ isn't there room for improvement here? Now I also need to test for records' existence. There are more efficient ways than doing a select on the record, even supported in SQL (the 'exists' function). What's the best way to test for record existence within the DAL ? Can I just do a 1>>> db(db.tbl.keyColumn==key).count() > 0 Is that efficient? -- Rb -- Rb On Jul 20, 3:18 pm, Hans Donner <hans.don...@pobox.com> wrote: > Hi, > > 1. Currently web2py relies for this on the DB engine > 2. Can always happen. There is no way to prevent this. > > The main risk with using technical keys is that too much is tied to > the id. They are *keys*, and should be used as such. > Eg when doing a max(id) and a min(id) no assumptions can be made that > there are max(id) - min(id) + 1 records are available. > > I've worked both with systems using technical keys (like web2py) and > natural keys. Both have their (dis)advantages. Web2py choose to use > technical keys and to reserve 'id' for this. > > Using *id* as the only key is a choice of the developer. You are free > to use another mechanism as well and use the value of the id as a kind > of row sequence number (or not if you do not trust it). > > And re upsetting mr Codd, there is probably always something that > might upset somebody. Googles bigtable (also supported by web2py) > might upset him, but for the scale on which it is used it outperforms > relational tables. > > Oh, and you can always skip the provided DAL and use something you prefer. > > On Mon, Jul 20, 2009 at 23:59, rb<rbspg...@gmail.com> wrote: > > >> Using this kind of technical keys (auto increment id, where > >> autoincrement is provided by db) is quite common fo db-design. In fact > > > Yes, and Codd himself said it is wrong, wrong, wrong to do so. Myself, > > I can only see a problem if/when: > > > 1. the id gets reused or > > > 2. if the record gets deleted and then later added in again with a > > different id. > > > I'm no db expert, but if the two things above were avoided then it > > would be pretty safe to use ids, no? I would like to know if I can > > _depend_ upon ids NOT getting reused - that would simplify things > > greatly. > > > I don't know about the second worry though. If it is possible to > > delete a record that another table's record is referring to (as a > > foreign key field) then it would cause corruption if that id were > > reused. Further, if the foreign key field referred to the proper *key* > > of the other table's row, rather than the id field, then when the row > > gets re-inserted later, the foreign key relationship would just find > > the new record. Using an id field, which could change, is > > problematical. > > >> So far no issues are reported regarding this. > > > Wonderful. Does this mean that my concerns are unfounded re (1) ? How > > about (2). For those (rare?) occasions where 2 could happen, does this > > lead to the problems that upset mister Codd? > > > --- > > Rb --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
