[web2py] Re: DAL() unusable ?

[Mirek Zvolský]

 Thadeus Burgess
 WRong. cr2 is a really really bad design.
 Database 101, any many to many relationship must be defined through a link 
 table.

I'm sorry, but you missunderstand the term many to many relationship
and the term relation at all.
Because, of course, one relation means exactly one relation = one
kind of relationship between two entities.
In other words one relation has exactly ONE meaning in the real
world, for which we create the model.

If we realy have ONE relation, which is many to many, then there is
NO POSSIBILITY, how to design this using 2 tables, and 3-rd link table
(with foreign keys to both previous) must be added. - Please think
about this to understand the difference: We have here ONE relation
from the real world, which we realize with TWO relations of the data
model, because we have no other way how to do it.

That's not the case of
http://zvolsky.alwaysdata.net/crm2.jpg

Here we have 3 relations which describe 3 different relationships
(relationships of different reasons!) between real entities:
1) which person is an employee of which company,
2) which person has created the company record,
3) which person has created the person records,
all 3 relations are 1:m relations.

To realize 1:m relation there is no need to add a connecting table
and if you do so, that would be a realy very bad design !
And it's easy to say why in other words:
Because in case of real m:m relation there can be more as one
records in the added connecting table to describe relationship between
two entities.
But if you will add connecting table from your weak reasons, then you
will have always one(!) record in the connecting table.
So you will have no benefit when you add such table, but you receive
lot of additional problems to handle the more difficult model.

I hope I have exlained it well with my bad english.
I'm sorry that I speak a little hard, this is not from the reason that
previous are basic things in relational databases and in SQL language,
but reason is that there is a risk, that you will avoid Massimo to
make that great change.

Mirek

[web2py] Re: DAL() unusable ?

[Mirek Zvolský]

 Mariano Reingart

Thanks for your response and practical experience with large system.

I think the problem is clear:
In current web2py philosophy the same data model becames valid or
invalid based on the order of table definition commands. And if a
model is complex, then there is no possibility how to make it valid/
acceptable for web2py.

So as long this situation will continue, so long there will be block
for more complex projects. The problem will come back again and again.
It will be nice, if Massimo(?) could redesign this soon in such way:
first create tables/fields in the db object, second turn on
constraints in cycle over all tables and over all their foreign keys.

Of course for large projects there will be a problem of too many
tables and slow execution, especially when structures changes.
 Massimo Di Pierro:
 there is a problem if too many tables are in scope. We need to create
 a mechanisn for conditionally executing models based on the called
 action. Perhaps give models a subfolder structure like in views.

Maybe this Massimo's idea is good. There should be always one model
(default=db.py) with full database definition, and just this model
would support structure migrations. In simple projects we could use
this model (same situation as today for all web2py projects), in large
projects subfolder models can be defined, which are smaller sub-sets
of the whole model - they would not support migration, but they just
will (together with controller) prepare data for specific view.

However, I don't know, if such system change in web2py would be good.
Because who need such something to speed up his model, he can test
request.controller and request.function in model and he can
conditional call what he need.

Re: [web2py] Re: DAL() unusable ?

[Thadeus Burgess]

Right. I get it now.

Is there a reason for not sticking the record creation inside of its own
table?

--
Thadeus




2010/11/12 Mirek Zvolský zvol...@seznam.cz

 hich person is an employee of which company,
 2) which person has created the company record,
 3) which person has created the person records,
 all 3 relations are 1:m relations.

 To realize 1:m relation there is no need to add a connecting table
 and if you do so, that would be a realy very bad design !
 And it's easy to say why in other words:
 Because in case of real m:m relation there can be more as one
 records in the added connecting table to describe relationship between
 two entities.
 But if you will add connecting table from your weak reasons, then you
 will have always one(!) record in the connecting tab

[web2py] Re: DAL() unusable ?

[Mirek Zvolský]

 Is there a reason for not sticking the record creation inside of its own
 table?

Hi Tadeus,
This is just an example. I have used it to show Massimo and others,
that the simplest part of CRM application model, if you change
created_by from type string to type reference.., then there is
no possibility of proper order of both tables in the model.

Of course, data can be arranged in lot of other ways.

If you think, that this example is wrong, maybe I should try find
other one? I will think about it. Problem is, that usually you find
such relation cycles in database schema, when the project is little
complex, lets say, if you work with 8, 10, 15 tables. Example from CRM
was pretty simple.

I just want to show, that I see the problem in the fact, that if you
change order of table definitions, you can make the same model legal
or illegal. I think this is really wrong.

If you think, that you can design every complex database model without
to have a circle of relations somewhere, I don't believe you are true.
If I'm true, web2py should be changed to obtain no error, when foreign
key is defined earlier as the target table.
If you are true, web2py should be changed to catch such error in the
model in all cases, not only if order of table definitions is
specific.
Mirek

Re: [web2py] Re: DAL() unusable ?

[Mariano Reingart]

2010/11/12 Mirek Zvolský zvol...@seznam.cz:
 Mariano Reingart

 Thanks for your response and practical experience with large system.

 I think the problem is clear:
 In current web2py philosophy the same data model becames valid or
 invalid based on the order of table definition commands. And if a
 model is complex, then there is no possibility how to make it valid/
 acceptable for web2py.

Yes, you can make any model valid to web2py, as Massimo said, just
don't use reference, use 'integer' as field type and IS_IN_DB
validator to change widgets and do lookups.

Foreign key constraints can be created and will be enforced at
database level, so basic migration and most things in web2py should
work the same.

 So as long this situation will continue, so long there will be block
 for more complex projects. The problem will come back again and again.
 It will be nice, if Massimo(?) could redesign this soon in such way:
 first create tables/fields in the db object, second turn on
 constraints in cycle over all tables and over all their foreign keys.

Or, may be not, for complex project we may include separate scripts to
do migrations and fixtures.

 Of course for large projects there will be a problem of too many
 tables and slow execution, especially when structures changes.
 Massimo Di Pierro:
 there is a problem if too many tables are in scope. We need to create
 a mechanisn for conditionally executing models based on the called
 action. Perhaps give models a subfolder structure like in views.

 Maybe this Massimo's idea is good. There should be always one model
 (default=db.py) with full database definition, and just this model
 would support structure migrations. In simple projects we could use
 this model (same situation as today for all web2py projects), in large
 projects subfolder models can be defined, which are smaller sub-sets
 of the whole model - they would not support migration, but they just
 will (together with controller) prepare data for specific view.

I don't think so.
Subfolder will add more complexity, further directories scan (with
performance penalities), and will make references, migrations and
fixtures even harder, fragmentating the model, etc.

 However, I don't know, if such system change in web2py would be good.
 Because who need such something to speed up his model, he can test
 request.controller and request.function in model and he can
 conditional call what he need.

I agree with this, advanced project will find the way (as an example,
I did it for the ERP, redefining the definition order and solving
other minor issues)

I think web2py should remain easy for begginers and most use cases,
and should allow complex ones to be possible, as now.

Regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

Re: [web2py] Re: DAL() unusable ?

[Branko Vukelic]

2010/11/12 Mirek Zvolský zvol...@seznam.cz:
 That's not the case of
 http://zvolsky.alwaysdata.net/crm2.jpg

DISCLAIMER: I'm not experienced with large-scale database designs.

Now that I've gotten that out of the way...

What's the meaning of 'created_by' in this set-up? If it's meant to be
taken as 'record created by user' than I don't see any reason why this
'user' must be in the 'person' table. Person as a concept is not the
same as the system user, and hence it's more logical to have a
separate table for system users that may or may not be a person in the
'person' sense, but is definitely a 'user' that can create records.

Person - can belong to - Company
Person - is also a - User
User - can create a - Person (that is not necessarily the same
person as the user)
User - can create a - Company

So we have this:

Person.created_by - User.id (User that created this person)
Person.user_id - User.id (in plain English: has an account on this system)
Company.created_by - User.id

In other words, we make a distinction between a real-world person, and
a system account.

Note that I'm not talking about how DAL works. I'm talking about how
the data is modelled in this particular case.


-- 
Branko Vukelić

bg.bra...@gmail.com
stu...@brankovukelic.com

Check out my blog: http://www.brankovukelic.com/
Check out my portfolio: http://www.flickr.com/photos/foxbunny/
Registered Linux user #438078 (http://counter.li.org/)
I hang out on identi.ca: http://identi.ca/foxbunny

Gimp Brushmakers Guild
http://bit.ly/gbg-group

[web2py] Re: DAL() unusable ?

[ron_m]

Cross reference between tables, see this post

http://groups.google.com/group/web2py/browse_thread/thread/b3cb1ce223649e0f/14688d53b3d88857?lnk=gstq=table+cross+reference#14688d53b3d88857


On Nov 11, 12:44 am, Mirek Zvolský zvol...@seznam.cz wrote:
 I have simplest model:

 db.define_table('company',
     Field('name'),
     Field('created_by', 'reference person'))
 db.define_table('person',
     Field('last_name'),
     Field('company_id', 'reference company'))

 but I receive error:
   File C:\Python27\Lib\site-packages\web2py\gluon\sql.py, line 1643,
 in _create_references
     raise SyntaxError, 'Table: table %s does not exist' % referenced

 So it looks not like a bug, but as behaviour by design (raise
 SyntaxError).

 What I do wrong?
 I simple need a standard possibilities of relations/joins, because I
 want design database application, not static html pages :-((

 Thanks for help. Mirek

[web2py] Re: DAL() unusable ?

[villas]

Here's a link to the section in the book:

http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

On Nov 11, 8:44 am, Mirek Zvolský zvol...@seznam.cz wrote:
 I have simplest model:

 db.define_table('company',
     Field('name'),
     Field('created_by', 'reference person'))
 db.define_table('person',
     Field('last_name'),
     Field('company_id', 'reference company'))

 but I receive error:
   File C:\Python27\Lib\site-packages\web2py\gluon\sql.py, line 1643,
 in _create_references
     raise SyntaxError, 'Table: table %s does not exist' % referenced

 So it looks not like a bug, but as behaviour by design (raise
 SyntaxError).

 What I do wrong?
 I simple need a standard possibilities of relations/joins, because I
 want design database application, not static html pages :-((

 Thanks for help. Mirek

[web2py] Re: DAL() unusable ?

[Mirek Zvolský]

Oh no!,no!,no! please no!
I still hope it's my mistake only. I simple cannot believe, that this
is behaviour of web2py data model. Are we in year 1960?
Friends, take a look to the oldest database implementations older as
SQL language, in MS-DOS times, f.e. dBase III, FoxBase, Clipper. And
earlier on the mainframes this was sure known and allowed too.
There were NEVER problems with such type of foreign keys! If we have
model with 5 tables, maybe we can avoid use of such foreign keys,
however if we have a realy bussiness application with about 50 tables?

I know I'm beginner here, however I have choosen web2py as a world
leading and best designed framework. Should I find somewhere else? Has
Django same basic problems with data model?

Or is there a workaround in web2py?
I have idea about 2 possible ways:


1)
Can I patch web2py to disable call of _create_references() after
separate table is added to the model? _create_references() are then to
be called after all tables are already added, in cycle through all
tables in the model.
However, this way is more for web2py authors as for me...


2)
Can I use standard 'integers' for foreign keys instead of
'reference..'?

I know that 'reference...' takes care for referential integrity. I
don't know, if cascade deleting is implemented on the web2py level or
on the database backend level. However, I think, this is one thing,
which is unusable in current implementation: Because OnDelete=Nothing
is unusable at all (breakes referential integrity), and
OnDelete=Cascade is not enough. Implementation of OnDelete=SetNull/
None is necessary -- so this is first point which I have to solve
regardless if I will use 'reference..' or 'integer' types.

And second problem is the widget for foreign key (for SQLFORM  Crud),
however I think I can assign same widget for 'integer' as for
'reference..'?

Is there something more, where 'reference..' type works better as
'integer'?

Thanks for responses and help.
Mirek


  db.define_table('company',
     Field('name'),
     Field('created_by', 'reference person'))
  db.define_table('person',
     Field('last_name'),
     Field('company_id', 'reference company'))
  but I receive error:
   File C:\Python27\Lib\site-packages\web2py\gluon\sql.py, line 1643,
  in _create_references
     raise SyntaxError, 'Table: table %s does not exist' % referenced

[web2py] Re: DAL() unusable ?

[Mirek Zvolský]

 Here's a link to the section in the book:
 http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

Yes, I know this, and from that reason I always use 'reference..'
style syntax.

However this is about very special foreign keys, which targets to same
table (to the primary key in same table).

And if there is some cycle in joins in the model (and of course it
always is, except of very, very simple models), this will not help.
And there is realy no solution for this in web2py ??

Re: [web2py] Re: DAL() unusable ?

[Thadeus Burgess]

Your error seems to be coming from the fact you do not have defined a person
model. Since web2py is a functional designed you must define person table
before you can reference it.

I use the following self-referential table and have never had a problem.

db.define_table('participant',
#... lots of other fields
  Field(referred_by, reference participant),
#... lots of other fields.
)

--
Thadeus




2010/11/11 Mirek Zvolský zvol...@seznam.cz

  Here's a link to the section in the book:
 
 http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

 Yes, I know this, and from that reason I always use 'reference..'
 style syntax.

 However this is about very special foreign keys, which targets to same
 table (to the primary key in same table).

 And if there is some cycle in joins in the model (and of course it
 always is, except of very, very simple models), this will not help.
 And there is realy no solution for this in web2py ??

[web2py] Re: DAL() unusable ?

[DenesL]

Some of you have missed Mirek's point, and he has a very valid one.
The problem is circular references.
There are ways around it but no elegant solution yet.

There was discussion some time ago about lazy evaluation of tables.

I also would like to see this solved in web2py.

Denes.


On Nov 11, 10:36 am, Mirek Zvolský zvol...@seznam.cz wrote:
  Here's a link to the section in the book:
 http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

 Yes, I know this, and from that reason I always use 'reference..'
 style syntax.

 However this is about very special foreign keys, which targets to same
 table (to the primary key in same table).

 And if there is some cycle in joins in the model (and of course it
 always is, except of very, very simple models), this will not help.
 And there is realy no solution for this in web2py ??

[web2py] Re: DAL() unusable ?

[David Marko]

Yes its the point.
When you simply switch the definition order to person table first,
web2py will raise the same error with company table doesnt exist yet.

David

On 11 lis, 17:05, DenesL denes1...@yahoo.ca wrote:
 Some of you have missed Mirek's point, and he has a very valid one.
 The problem is circular references.
 There are ways around it but no elegant solution yet.

 There was discussion some time ago about lazy evaluation of tables.

 I also would like to see this solved in web2py.

 Denes.

 On Nov 11, 10:36 am, Mirek Zvolský zvol...@seznam.cz wrote:







   Here's a link to the section in the book:
  http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

  Yes, I know this, and from that reason I always use 'reference..'
  style syntax.

  However this is about very special foreign keys, which targets to same
  table (to the primary key in same table).

  And if there is some cycle in joins in the model (and of course it
  always is, except of very, very simple models), this will not help.
  And there is realy no solution for this in web2py ??

[web2py] Re: DAL() unusable ?

[mdipierro]

From a syntactical point of view it would not take much to support
multi-table circular references in web2py. That is not why they are
not implemented, They are not implemented because they are bad design
practice.

If this is a 1-1 relation, there is no need to table A to refer to B
and B to A. It is sufficient that A points to B.

If this is a many-many this should be done via a link table.

I cannot think of any test case when this is a good idea. web2py sets
constraints for the purpose of enforcing good practice.

Massimo


On Nov 11, 10:05 am, DenesL denes1...@yahoo.ca wrote:
 Some of you have missed Mirek's point, and he has a very valid one.
 The problem is circular references.
 There are ways around it but no elegant solution yet.

 There was discussion some time ago about lazy evaluation of tables.

 I also would like to see this solved in web2py.

 Denes.

 On Nov 11, 10:36 am, Mirek Zvolský zvol...@seznam.cz wrote:

   Here's a link to the section in the book:
  http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

  Yes, I know this, and from that reason I always use 'reference..'
  style syntax.

  However this is about very special foreign keys, which targets to same
  table (to the primary key in same table).

  And if there is some cycle in joins in the model (and of course it
  always is, except of very, very simple models), this will not help.
  And there is realy no solution for this in web2py ??

[web2py] Re: DAL() unusable ?

[DenesL]

I was thinking of scripted table creation and keeping things simple
but I understand what you are saying (or reiterating since it has been
said before).

If no valid case is made then I could change the manual to explain why
it is not supported.
Pros/Cons?

Denes.

On Nov 11, 11:18 am, mdipierro mdipie...@cs.depaul.edu wrote:
 From a syntactical point of view it would not take much to support
 multi-table circular references in web2py. That is not why they are
 not implemented, They are not implemented because they are bad design
 practice.

 If this is a 1-1 relation, there is no need to table A to refer to B
 and B to A. It is sufficient that A points to B.

 If this is a many-many this should be done via a link table.

 I cannot think of any test case when this is a good idea. web2py sets
 constraints for the purpose of enforcing good practice.

 Massimo

 On Nov 11, 10:05 am, DenesL denes1...@yahoo.ca wrote:

  Some of you have missed Mirek's point, and he has a very valid one.
  The problem is circular references.
  There are ways around it but no elegant solution yet.

  There was discussion some time ago about lazy evaluation of tables.

  I also would like to see this solved in web2py.

  Denes.

  On Nov 11, 10:36 am, Mirek Zvolský zvol...@seznam.cz wrote:

Here's a link to the section in the book:
   http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

   Yes, I know this, and from that reason I always use 'reference..'
   style syntax.

   However this is about very special foreign keys, which targets to same
   table (to the primary key in same table).

   And if there is some cycle in joins in the model (and of course it
   always is, except of very, very simple models), this will not help.
   And there is realy no solution for this in web2py ??

[web2py] Re: DAL() unusable ?

[mdipierro]

BTW. This is possible:

db.define_table('company',
Field('name'),
Field('created_by', 'integer'))
db.define_table('person',
Field('last_name'),
Field('company_id', 'reference company'))

db.company.created_by.requires=IS_IN_DB(db,'person.id','%
(last_name)s')

although my object stands.

On Nov 11, 10:46 am, DenesL denes1...@yahoo.ca wrote:
 I was thinking of scripted table creation and keeping things simple
 but I understand what you are saying (or reiterating since it has been
 said before).

 If no valid case is made then I could change the manual to explain why
 it is not supported.
 Pros/Cons?

 Denes.

 On Nov 11, 11:18 am, mdipierro mdipie...@cs.depaul.edu wrote:

  From a syntactical point of view it would not take much to support
  multi-table circular references in web2py. That is not why they are
  not implemented, They are not implemented because they are bad design
  practice.

  If this is a 1-1 relation, there is no need to table A to refer to B
  and B to A. It is sufficient that A points to B.

  If this is a many-many this should be done via a link table.

  I cannot think of any test case when this is a good idea. web2py sets
  constraints for the purpose of enforcing good practice.

  Massimo

  On Nov 11, 10:05 am, DenesL denes1...@yahoo.ca wrote:

   Some of you have missed Mirek's point, and he has a very valid one.
   The problem is circular references.
   There are ways around it but no elegant solution yet.

   There was discussion some time ago about lazy evaluation of tables.

   I also would like to see this solved in web2py.

   Denes.

   On Nov 11, 10:36 am, Mirek Zvolský zvol...@seznam.cz wrote:

 Here's a link to the section in the book:
http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

Yes, I know this, and from that reason I always use 'reference..'
style syntax.

However this is about very special foreign keys, which targets to same
table (to the primary key in same table).

And if there is some cycle in joins in the model (and of course it
always is, except of very, very simple models), this will not help.
And there is realy no solution for this in web2py ??

[web2py] Re: DAL() unusable ?

[DenesL]

Wasn't this a no-no?.
I got wrist slapped once for suggesting it. :)


On Nov 11, 11:53 am, mdipierro mdipie...@cs.depaul.edu wrote:
 BTW. This is possible:

 db.define_table('company',
 Field('name'),
 Field('created_by', 'integer'))
 db.define_table('person',
 Field('last_name'),
 Field('company_id', 'reference company'))

 db.company.created_by.requires=IS_IN_DB(db,'person.id','%
 (last_name)s')

 although my object stands.

 On Nov 11, 10:46 am, DenesL denes1...@yahoo.ca wrote:

  I was thinking of scripted table creation and keeping things simple
  but I understand what you are saying (or reiterating since it has been
  said before).

  If no valid case is made then I could change the manual to explain why
  it is not supported.
  Pros/Cons?

  Denes.

  On Nov 11, 11:18 am, mdipierro mdipie...@cs.depaul.edu wrote:

   From a syntactical point of view it would not take much to support
   multi-table circular references in web2py. That is not why they are
   not implemented, They are not implemented because they are bad design
   practice.

   If this is a 1-1 relation, there is no need to table A to refer to B
   and B to A. It is sufficient that A points to B.

   If this is a many-many this should be done via a link table.

   I cannot think of any test case when this is a good idea. web2py sets
   constraints for the purpose of enforcing good practice.

   Massimo

   On Nov 11, 10:05 am, DenesL denes1...@yahoo.ca wrote:

Some of you have missed Mirek's point, and he has a very valid one.
The problem is circular references.
There are ways around it but no elegant solution yet.

There was discussion some time ago about lazy evaluation of tables.

I also would like to see this solved in web2py.

Denes.

On Nov 11, 10:36 am, Mirek Zvolský zvol...@seznam.cz wrote:

  Here's a link to the section in the book:
 http://www.web2py.com/book/default/chapter/06#Self-Reference-and-Aliases

 Yes, I know this, and from that reason I always use 'reference..'
 style syntax.

 However this is about very special foreign keys, which targets to same
 table (to the primary key in same table).

 And if there is some cycle in joins in the model (and of course it
 always is, except of very, very simple models), this will not help.
 And there is realy no solution for this in web2py ??

[web2py] Re: DAL() unusable ?

[Mirek Zvolský]

- mdipierro
I have prepared 2 pictures, and in this time there is new post from
you about company/author. Interesting that my pictures are about the
same :-)

Model of your CRM application application is here:
http://zvolsky.alwaysdata.net/crm1.jpg
Such model can be implemented in web2py, but order of definitions is
important: auth_user, company, person

But let's think, you want all people from person's table can register/
login into your application. In such case you will follow web2py book,
chapter 8,
http://web2py.com/book/default/chapter/08, Customizing Auth  Renaming
Auth Tables,
and you will receive following model:
http://zvolsky.alwaysdata.net/crm2.jpg

And using reference... field types, in both definition orders
(company,person or person,company) web2py will fail.

As you say, I can use 'integer' type to avoid this problem, where it
is. But mixing 'reference..' and 'integer' for same goal would be
strange. So I can use 'integer' for all foreign keys in database. But
that means in other words, that the 'reference..' field type is for
nothing.

What about a more difficult model as
http://zvolsky.alwaysdata.net/crm3.jpg
I'm sorry, this is not in english. However it is something about
organization of courses, lessons, we have some teachers and
partitipant here, some payments for whole course of for one lesson
only, and so on.

Generated web2py code from OndrejZara's web designer is here:
http://zvolsky.alwaysdata.net/jekus.py

And of course this code will fail in web2py, because of there is no
possibility to find a proper order of table definitions.
Please, think about this. The change in web2py in this direction would
be great.

Best regards, Mirek

[web2py] Re: DAL() unusable ?

[mdipierro]

Ok. Your crm2jpg makes a good case. You proved me wrong.
I will try add this by the week-end.

Massimo

On Nov 11, 11:24 am, Mirek Zvolský zvol...@seznam.cz wrote:
 - mdipierro
 I have prepared 2 pictures, and in this time there is new post from
 you about company/author. Interesting that my pictures are about the
 same :-)

 Model of your CRM application application is 
 here:http://zvolsky.alwaysdata.net/crm1.jpg
 Such model can be implemented in web2py, but order of definitions is
 important: auth_user, company, person

 But let's think, you want all people from person's table can register/
 login into your application. In such case you will follow web2py book,
 chapter 8,http://web2py.com/book/default/chapter/08, Customizing Auth  
 Renaming
 Auth Tables,
 and you will receive following model:http://zvolsky.alwaysdata.net/crm2.jpg

 And using reference... field types, in both definition orders
 (company,person or person,company) web2py will fail.

 As you say, I can use 'integer' type to avoid this problem, where it
 is. But mixing 'reference..' and 'integer' for same goal would be
 strange. So I can use 'integer' for all foreign keys in database. But
 that means in other words, that the 'reference..' field type is for
 nothing.

 What about a more difficult model ashttp://zvolsky.alwaysdata.net/crm3.jpg
 I'm sorry, this is not in english. However it is something about
 organization of courses, lessons, we have some teachers and
 partitipant here, some payments for whole course of for one lesson
 only, and so on.

 Generated web2py code from OndrejZara's web designer is 
 here:http://zvolsky.alwaysdata.net/jekus.py

 And of course this code will fail in web2py, because of there is no
 possibility to find a proper order of table definitions.
 Please, think about this. The change in web2py in this direction would
 be great.

 Best regards, Mirek

Re: [web2py] Re: DAL() unusable ?

[Thadeus Burgess]

WRong. cr2 is a really really bad design.

Database 101, any many to many relationship must be defined through a link
table.

Redesign CR2 like so...


db.define_table('person', created_by('person'))
db.define_table('company',...created_by('company'))

db.define_table('person_company', id_person('person'),
id_company('company'))

If you want to knwo what company ap person belongs to, look it up in the
link table

person = db.person.first()
link = db.person_company.id_person == person.id
company = db.company.id == link.id_company

This is the proper way to design this type of relationship.

I would opt more for a feature in web2py that did this in the background
than handled circular dependency magick.

--
Thadeus




On Thu, Nov 11, 2010 at 11:32 AM, mdipierro mdipie...@cs.depaul.edu wrote:

 Ok. Your crm2jpg makes a good case. You proved me wrong.
 I will try add this by the week-end.

 Massimo

 On Nov 11, 11:24 am, Mirek Zvolský zvol...@seznam.cz wrote:
  - mdipierro
  I have prepared 2 pictures, and in this time there is new post from
  you about company/author. Interesting that my pictures are about the
  same :-)
 
  Model of your CRM application application is here:
 http://zvolsky.alwaysdata.net/crm1.jpg
  Such model can be implemented in web2py, but order of definitions is
  important: auth_user, company, person
 
  But let's think, you want all people from person's table can register/
  login into your application. In such case you will follow web2py book,
  chapter 8,http://web2py.com/book/default/chapter/08, Customizing Auth 
 Renaming
  Auth Tables,
  and you will receive following model:
 http://zvolsky.alwaysdata.net/crm2.jpg
 
  And using reference... field types, in both definition orders
  (company,person or person,company) web2py will fail.
 
  As you say, I can use 'integer' type to avoid this problem, where it
  is. But mixing 'reference..' and 'integer' for same goal would be
  strange. So I can use 'integer' for all foreign keys in database. But
  that means in other words, that the 'reference..' field type is for
  nothing.
 
  What about a more difficult model ashttp://
 zvolsky.alwaysdata.net/crm3.jpg
  I'm sorry, this is not in english. However it is something about
  organization of courses, lessons, we have some teachers and
  partitipant here, some payments for whole course of for one lesson
  only, and so on.
 
  Generated web2py code from OndrejZara's web designer is here:
 http://zvolsky.alwaysdata.net/jekus.py
 
  And of course this code will fail in web2py, because of there is no
  possibility to find a proper order of table definitions.
  Please, think about this. The change in web2py in this direction would
  be great.
 
  Best regards, Mirek

[web2py] Re: DAL() unusable ?

[mdipierro]

I agree with you that using a link table is better. This was my
original argument.

Yet often the need to sign tables (created_by) will suddenly cause a
duplication of tables because lots of link tables have to be added and
queries changed. This is what I meant by good case.

Massimo

On Nov 11, 12:05 pm, Thadeus Burgess thade...@thadeusb.com wrote:
 WRong. cr2 is a really really bad design.

 Database 101, any many to many relationship must be defined through a link
 table.

 Redesign CR2 like so...

 db.define_table('person', created_by('person'))
 db.define_table('company',...created_by('company'))

 db.define_table('person_company', id_person('person'),
 id_company('company'))

 If you want to knwo what company ap person belongs to, look it up in the
 link table

 person = db.person.first()
 link = db.person_company.id_person == person.id
 company = db.company.id == link.id_company

 This is the proper way to design this type of relationship.

 I would opt more for a feature in web2py that did this in the background
 than handled circular dependency magick.

 --
 Thadeus

 On Thu, Nov 11, 2010 at 11:32 AM, mdipierro mdipie...@cs.depaul.edu wrote:
  Ok. Your crm2jpg makes a good case. You proved me wrong.
  I will try add this by the week-end.

  Massimo

  On Nov 11, 11:24 am, Mirek Zvolský zvol...@seznam.cz wrote:
   - mdipierro
   I have prepared 2 pictures, and in this time there is new post from
   you about company/author. Interesting that my pictures are about the
   same :-)

   Model of your CRM application application is here:
 http://zvolsky.alwaysdata.net/crm1.jpg
   Such model can be implemented in web2py, but order of definitions is
   important: auth_user, company, person

   But let's think, you want all people from person's table can register/
   login into your application. In such case you will follow web2py book,
   chapter 8,http://web2py.com/book/default/chapter/08, Customizing Auth 
  Renaming
   Auth Tables,
   and you will receive following model:
 http://zvolsky.alwaysdata.net/crm2.jpg

   And using reference... field types, in both definition orders
   (company,person or person,company) web2py will fail.

   As you say, I can use 'integer' type to avoid this problem, where it
   is. But mixing 'reference..' and 'integer' for same goal would be
   strange. So I can use 'integer' for all foreign keys in database. But
   that means in other words, that the 'reference..' field type is for
   nothing.

   What about a more difficult model ashttp://
  zvolsky.alwaysdata.net/crm3.jpg
   I'm sorry, this is not in english. However it is something about
   organization of courses, lessons, we have some teachers and
   partitipant here, some payments for whole course of for one lesson
   only, and so on.

   Generated web2py code from OndrejZara's web designer is here:
 http://zvolsky.alwaysdata.net/jekus.py

   And of course this code will fail in web2py, because of there is no
   possibility to find a proper order of table definitions.
   Please, think about this. The change in web2py in this direction would
   be great.

   Best regards, Mirek

Re: [web2py] Re: DAL() unusable ?

[Thadeus Burgess]

I still do not agree.

If you run into this issue you can always offset the created_by fields...

db.define_table('audit_table',

Field('tablename'), Field('record_id', 'integer'),

Field('created_by', db.auth_user),

)

If you ever did need to audit, just look it up in this table. Then you don't
have the problem at all.

--
Thadeus




On Thu, Nov 11, 2010 at 12:17 PM, mdipierro mdipie...@cs.depaul.edu wrote:

 I agree with you that using a link table is better. This was my
 original argument.

 Yet often the need to sign tables (created_by) will suddenly cause a
 duplication of tables because lots of link tables have to be added and
 queries changed. This is what I meant by good case.

 Massimo

 On Nov 11, 12:05 pm, Thadeus Burgess thade...@thadeusb.com wrote:
  WRong. cr2 is a really really bad design.
 
  Database 101, any many to many relationship must be defined through a
 link
  table.
 
  Redesign CR2 like so...
 
  db.define_table('person', created_by('person'))
  db.define_table('company',...created_by('company'))
 
  db.define_table('person_company', id_person('person'),
  id_company('company'))
 
  If you want to knwo what company ap person belongs to, look it up in the
  link table
 
  person = db.person.first()
  link = db.person_company.id_person == person.id
  company = db.company.id == link.id_company
 
  This is the proper way to design this type of relationship.
 
  I would opt more for a feature in web2py that did this in the background
  than handled circular dependency magick.
 
  --
  Thadeus
 
  On Thu, Nov 11, 2010 at 11:32 AM, mdipierro mdipie...@cs.depaul.edu
 wrote:
   Ok. Your crm2jpg makes a good case. You proved me wrong.
   I will try add this by the week-end.
 
   Massimo
 
   On Nov 11, 11:24 am, Mirek Zvolský zvol...@seznam.cz wrote:
- mdipierro
I have prepared 2 pictures, and in this time there is new post from
you about company/author. Interesting that my pictures are about the
same :-)
 
Model of your CRM application application is here:
  http://zvolsky.alwaysdata.net/crm1.jpg
Such model can be implemented in web2py, but order of definitions is
important: auth_user, company, person
 
But let's think, you want all people from person's table can
 register/
login into your application. In such case you will follow web2py
 book,
chapter 8,http://web2py.com/book/default/chapter/08, Customizing
 Auth 
   Renaming
Auth Tables,
and you will receive following model:
  http://zvolsky.alwaysdata.net/crm2.jpg
 
And using reference... field types, in both definition orders
(company,person or person,company) web2py will fail.
 
As you say, I can use 'integer' type to avoid this problem, where it
is. But mixing 'reference..' and 'integer' for same goal would be
strange. So I can use 'integer' for all foreign keys in database. But
that means in other words, that the 'reference..' field type is for
nothing.
 
What about a more difficult model ashttp://
   zvolsky.alwaysdata.net/crm3.jpg
I'm sorry, this is not in english. However it is something about
organization of courses, lessons, we have some teachers and
partitipant here, some payments for whole course of for one lesson
only, and so on.
 
Generated web2py code from OndrejZara's web designer is here:
  http://zvolsky.alwaysdata.net/jekus.py
 
And of course this code will fail in web2py, because of there is no
possibility to find a proper order of table definitions.
Please, think about this. The change in web2py in this direction
 would
be great.
 
Best regards, Mirek
 
 

[web2py] Re: DAL() unusable ?

[mdipierro]

True.

On Nov 11, 1:23 pm, Thadeus Burgess thade...@thadeusb.com wrote:
 I still do not agree.

 If you run into this issue you can always offset the created_by fields...

 db.define_table('audit_table',

 Field('tablename'), Field('record_id', 'integer'),

 Field('created_by', db.auth_user),

 )

 If you ever did need to audit, just look it up in this table. Then you don't
 have the problem at all.

 --
 Thadeus

 On Thu, Nov 11, 2010 at 12:17 PM, mdipierro mdipie...@cs.depaul.edu wrote:
  I agree with you that using a link table is better. This was my
  original argument.

  Yet often the need to sign tables (created_by) will suddenly cause a
  duplication of tables because lots of link tables have to be added and
  queries changed. This is what I meant by good case.

  Massimo

  On Nov 11, 12:05 pm, Thadeus Burgess thade...@thadeusb.com wrote:
   WRong. cr2 is a really really bad design.

   Database 101, any many to many relationship must be defined through a
  link
   table.

   Redesign CR2 like so...

   db.define_table('person', created_by('person'))
   db.define_table('company',...created_by('company'))

   db.define_table('person_company', id_person('person'),
   id_company('company'))

   If you want to knwo what company ap person belongs to, look it up in the
   link table

   person = db.person.first()
   link = db.person_company.id_person == person.id
   company = db.company.id == link.id_company

   This is the proper way to design this type of relationship.

   I would opt more for a feature in web2py that did this in the background
   than handled circular dependency magick.

   --
   Thadeus

   On Thu, Nov 11, 2010 at 11:32 AM, mdipierro mdipie...@cs.depaul.edu
  wrote:
Ok. Your crm2jpg makes a good case. You proved me wrong.
I will try add this by the week-end.

Massimo

On Nov 11, 11:24 am, Mirek Zvolský zvol...@seznam.cz wrote:
 - mdipierro
 I have prepared 2 pictures, and in this time there is new post from
 you about company/author. Interesting that my pictures are about the
 same :-)

 Model of your CRM application application is here:
   http://zvolsky.alwaysdata.net/crm1.jpg
 Such model can be implemented in web2py, but order of definitions is
 important: auth_user, company, person

 But let's think, you want all people from person's table can
  register/
 login into your application. In such case you will follow web2py
  book,
 chapter 8,http://web2py.com/book/default/chapter/08, Customizing
  Auth 
Renaming
 Auth Tables,
 and you will receive following model:
   http://zvolsky.alwaysdata.net/crm2.jpg

 And using reference... field types, in both definition orders
 (company,person or person,company) web2py will fail.

 As you say, I can use 'integer' type to avoid this problem, where it
 is. But mixing 'reference..' and 'integer' for same goal would be
 strange. So I can use 'integer' for all foreign keys in database. But
 that means in other words, that the 'reference..' field type is for
 nothing.

 What about a more difficult model ashttp://
zvolsky.alwaysdata.net/crm3.jpg
 I'm sorry, this is not in english. However it is something about
 organization of courses, lessons, we have some teachers and
 partitipant here, some payments for whole course of for one lesson
 only, and so on.

 Generated web2py code from OndrejZara's web designer is here:
   http://zvolsky.alwaysdata.net/jekus.py

 And of course this code will fail in web2py, because of there is no
 possibility to find a proper order of table definitions.
 Please, think about this. The change in web2py in this direction
  would
 be great.

 Best regards, Mirek

Re: [web2py] Re: DAL() unusable ?

[Mariano Reingart]

2010/11/11 Mirek Zvolský zvol...@seznam.cz:
 Oh no!,no!,no! please no!
 I still hope it's my mistake only. I simple cannot believe, that this
 is behaviour of web2py data model. Are we in year 1960?
 Friends, take a look to the oldest database implementations older as
 SQL language, in MS-DOS times, f.e. dBase III, FoxBase, Clipper. And
 earlier on the mainframes this was sure known and allowed too.
 There were NEVER problems with such type of foreign keys! If we have
 model with 5 tables, maybe we can avoid use of such foreign keys,
 however if we have a realy bussiness application with about 50 tables?

I agree, I have an ERP system with +70 tables, it very hard to define
models in order.
Look at this thread:
http://groups.google.com/group/web2py-
developers/browse_thread/thread/82c2128be252721a#

 I know I'm beginner here, however I have choosen web2py as a world
 leading and best designed framework. Should I find somewhere else? Has
 Django same basic problems with data model?

No, it has other problems (ie., it don't have automatic migrations nor
automatic lookups,  that both eases the reference issue), you can
define references as string (like web2py reference ...), and the
table may not be defined.

The issue is not that simple, it affect migrations and fixtures.

 Or is there a workaround in web2py?
 I have idea about 2 possible ways:


 1)
 Can I patch web2py to disable call of _create_references() after
 separate table is added to the model? _create_references() are then to
 be called after all tables are already added, in cycle through all
 tables in the model.

I've tried that too, but it solves part of the problem.
As I said, migrations should be done in two part (first creating
table, second appling constraints), so that is a more complex solution
and patch.

 2)
 Can I use standard 'integers' for foreign keys instead of
 'reference..'?

Yes, indeed you may have to do that anyway, as automatic migrations
may not work for complex models like this (by now).

Regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com