Circular table reference

[Markus Schmitz]

Hi everybody,

After evaluating quite a number of web frameworks, I ended up with
web2py and frankly I am more than impressed. It rocks in many ways
(could talk about it for some time really) and fits very close to my
internal requirements.

But I am a newbee and I am running into newbee problems:

While creating the needed data model, I got stuck with a circular
reference, which I can not resolve. This might be simply a problem
with a bad design of my data model, but I will describe it anyway.

I have port_agents, which are assigned to specific ports. One port
might have several port_agents.
At the same time each port might have a preferred agent, so my model
looks something like this:

db.define_table('ports',
Field('name','string'),
Field('port_agent_id','references port_agents') # this references
ahead and does not work
)

db.define_table('port_agents',
field('name','string',
Field('port_id', db.ports),
)

How can this be done in web2py?

I could use a field 'is preferred agent' on the port agent, but then I
have to build checks, that nobody else is a preferred agent for this
port etc.

Any suggestions?

Regards

Markus

[mdipierro]

Thank you Markus,

technically they are not allowed but you can do with a trick

db.define_table('ports',
Field('name','string'),

Field('port_agent_id','integer')

)
db.define_table('port_agents',
field('name','string',
Field('port_id', db.ports),
)

db.ports.port_agent_id.requires=IS_IN_DB(db.'port_agents.id','%(name)
s')
db.port_agents.port_id.requires=IS_IN_DB(db.'ports.id','%(name)s')

The port_agent_id reference would not be enforced by the database but
by web2py. On cascade delete would not work. Everything else would.

[mdipierro]

On a second thought.... why is this not supported?

Because I am not convinced this is a good idea.

If every record of table A only references one record of table B and
vice versa then perhaps there should be only one table AB.

If this is a one (A) to many (B) relation then A cannot contain a
reference to B because would not be unique.

If this is a many to many then there should be a link table. In your
case:

db.define_table('ports',
Field('name','string')

)
db.define_table('agents',
field('name','string')
)
db.define_table('port_agents',
Field('port_id', db.ports),
Field('agent_id',db.agents)
)

It is also possible that I do not understand the problem you are
working on and I am completely wrong.

Massimo

[Yarko Tymciurak]

On Thu, Oct 8, 2009 at 3:59 AM, Markus Schmitz <msch...@soft-impact.com> wrote:

Hi everybody,

.....

looks something like this:

db.define_table('ports',
   Field('name','string'),
   Field('port_agent_id','references port_agents')  # this references
ahead and does not work

You have a typo here - this needs to be: ... ,'reference port_agents')  # NOT  references - see p. 178 of the manual for examples;

   )

- Yarko 

[Yarko Tymciurak]

On Thu, Oct 8, 2009 at 9:06 AM, mdipierro <mdip...@cs.depaul.edu> wrote:

On a second thought.... why is this not supported?

Because I am not convinced this is a good idea.

If every record of table A only references one record of table B and
vice versa then perhaps there should be only one table AB. 

If this is a one (A) to many (B) relation then A cannot contain a
reference to B because would not be unique.

If this is a many to many then there should be a link table. In your
case:

db.define_table('ports',
    Field('name','string')

   )
 db.define_table('agents',
   field('name','string')

   )
 db.define_table('port_agents',

   Field('port_id', db.ports),

   Field('agent_id',db.agents)
 )

Using your own statement:

I have port_agents, which are assigned to specific ports. One port
might have several port_agents.
At the same time each port might have a preferred agent,

I think it is reasonable to assume that an agent may move between ports (even if only on rare occaision), so Massimo's example tables are - I think - good.  The only missing thing is somthing for your last statement,  and you will want to think about what makes more sense - is this an attribute of the port, or is this an attribute of the port_agents relationship.  I would probably go for the latter, and add an attribute for "preferred" pairing, but then you would have the possibility of many "preferred"s, so adding a Field('preferred_agent_id', db.agents)  to ports might be the way you will prefer (as it will limit you to a one-to-many relationship at worst).

Yarko

[mdipierro]

I have to admit I read Markus too fast and I missed that point.

On Oct 8, 9:33 am, Yarko Tymciurak <yark...@gmail.com> wrote:

[Markus Schmitz]

Hi Massimo,

I think the answers are (all) spot on. I will go for the "trick"
solution with the integer type instead of reference.

It is closest to what I had in mind. Not naturally a clean DB design,
but I can live with the missing "cascade on delete" in this
application scenario. So I am fione.

Thanks

a lot for the help,

Markus

[Yarko Tymciurak]

On Fri, Oct 9, 2009 at 2:32 AM, Markus Schmitz <msch...@soft-impact.com> wrote:

Hi Massimo,

I think the answers are (all) spot on. I will go for the "trick"
solution with the integer type instead of reference.

why??

If you use Massimo's 3 table layout, there is absolutely no need....