one to many relations

[pmate]

Hi,
in my model i have:

db.define_table('violazioni',
SQLField('codice','integer',requires=IS_NOT_IN_DB
(db,'violazioni.codice',error_message="codice duplicato")),
SQLField('infrazione','text',requires=IS_NOT_EMPTY()),
SQLField('note','text'))

db.define_table('sanzioni',
SQLField('violazioni_codice',db.violazioni),
SQLField('descrizione','text'),
SQLField('sanzione',length=32),
SQLField('note','text'))

but, of course, it doesn't work. What i want is to make a one to many
relation on 'codice' field. I know that if i put
'violazioni_id',db.violazioni
it works fine, but i need to make the relation on 'codice' field.
How can i do?

[DenesL]

pmate, what you probably want is to show the codice (not the id) but
the relation is still built on the id:

db.define_table('sanzioni',
SQLField('violazioni_id',db.violazioni),

SQLField('descrizione','text'),
SQLField('sanzione',length=32),
SQLField('note','text'))

db.sanzioni.violazioni_id.requires=IS_IN_DB
(db,'violazioni.id','violazioni.codice')

You can name the field 'violazioni_codice' or something else but the
value stored is the id of the violazioni.

[pmate]

Thanks for your quick replay.
My problem is there: i don't want the relation on id of violazioni
because if i delete a record the id sequence would change. For example
in violazioni:
id codice violazione
1 1 something
2 2 other thing
3 3 an other thing

Well, if i delete id nr.2 of violazioni and insert a new record, i
want:
1 1 something
3 3 an other thing
4 2 new insert

so that i can choose my codice value (to be related on records of
table sanzioni (one to many) and not to be forced to have them
incremented like in field id would be.

Hope to be clear...

[mdipierro]

You cannot do it. I do not consider this a good software engineering
practice. You reference a record, not a field. The record should
therefore be referenced by its unique id.

Different is the issue of representing the reference field. You can
make a join and display codice instead of id in views.

Massimo

[pmate]

Yes, but in this case field codice is unique too. The only difference
is that i want to choose its value, not the autoincrement of an id
field.

Paolo

[mdipierro]

I stand by my answer. Let me elaborate on it

1) do what Denes suggest:

db.define_table('sanzioni',
SQLField('violazioni_id',db.violazioni),
SQLField('descrizione','text'),
SQLField('sanzione',length=32),
SQLField('note','text'))

db.sanzioni.violazioni_id.requires=IS_IN_DB
(db,'violazioni.id','violazioni.codice')

and in all forms, codice will be shows instead of id.

2) in select:

for row in db(db.sanzioni.violazioni_id==db.violazioni.id).select(): #
do a join
print row.sanzioni.id, row.violazioni.codice

I religiously believe it is the job of the database to create unique
keys, not a job of the user.

Massimo

[mdipierro]

PS.
Of course you can also do:

db.define_table('sanzioni',
SQLField('violazioni_codice'),

SQLField('descrizione','text'),
SQLField('sanzione',length=32),
SQLField('note','text'))

db.sanzioni.violazioni_codice.requires=IS_IN_DB
(db,'violazioni.codice','%(codice)s')

and let web2py, not the db, enforce your relation.

[pmate]

Your second solution is exactly that one that i have chosen as
alternative to my first question.
Thanks a lot

Paolo

[DenesL]

Note: with the second solution a record from table violazioni will not
have an autocreated sanzioni attribute which allows things like:

for v in db().select(db.violazioni.ALL):
print v.codice
for s in v.sanzioni.select():
print ' ',s.sanzione

[mdipierro]

Should we allow references other than to the id field?

Massimo

[pmate]

yes Denesi, that's the point.
Usually indexes in a table can be more than one. And they are useful
for the speed of queries and for foreign relationship, i think

[annet]

I do not understand the Italian words in your posts, but I think I am
facing the same problem. My problem is narrowed down to
standardization tables. In most cases I implement the first solution,
that is when the content of standardization tables is not displayed in
the view and only bears relevance in queries. However, in my address
table and service table I use the second solution, to link a city to a
standardization table with city names and to link services to a
standardization table with service names.

The second solution makes displaying addresses and services a lot
easier, and, I guess, enables me to implement the city and service
fields as auto-complete fields (a problem I have not solved yet), not
as drop boxes. However, the downside is that when I update a city or
service in their respective standardization tables the update is not
cascaded. onupdate='CASCADE' is not supported in web2py, I added the
ON UPDATE CASCADE behavior in the database, hoping this would not
interfere with web2py. So far, I did not run into any problems, but I
still have my doubts when it comes to the beauty of the second
solution.


Annet.

[billf]

The only reason I can see for allowing references on a field other
than id is if you are de-normalizing, i.e. duplicating some data from
a reference table into your primary table to save a join. I'm not
sure how common this is and I would be tempted to stipulate that the
reference was still by id (so have both id and duplicated field in
primary table).

Pmate: with respect, how does referencing by id or field affect
indices in respect of speed and foreign relationship? (apart from if
the non-id is text it will probably be a little slower).

> Should we allow references other than to the id field?

[achipa]

Not sure if it applies here, but you can have multi-column (primary)
keys in some DBMS and that can cause some headache with regard to
id's.

[billf]

achipa

you're right but isn't the second rule of web2py "every table must
have a unique id field".

(the first rule of web2py being "you do talk about web2py")

[pmate]

Billf: if you read well my post you see that i never spoke about any
affectation. All i meant it was that i didn't see anything strange in
creating more indexes in the same table (different from id). And that
i didn't see anything strange in creating relationship with these
indexes. I said what indexes are for and not that referencing by one
field would improve speed better than referencing by an other. Never
thought.
Never thought neither to set an index other than integer (as in my
example).
Massimo answered that he didn't provide this functionality because he
doesn't consider it a good software engineering practice.
That's all

[DenesL]

Annet,

On Dec 1, 3:57 am, annet <jmverm...@planet.nl> wrote:
> The second solution makes displaying addresses and services a lot
> easier, and, I guess, enables me to implement the city and service
> fields as auto-complete fields (a problem I have not solved yet), not
> as drop boxes. However, the downside is that when I update a city or
> service in their respective standardization tables the update is not
> cascaded.

The second solution would make it easier to display the created
records, since there is no need for an additional DB lookup (using the
id to get the record content), it makes no difference for the drop
down display.

Since you are not keeping the reference to the record (only a part of
the record, one field in this case) you don't get the updated info
without doing a DB search (to obtain the id) and then you would have
to handle the missing record case (not to mention DB integrity). An
onupdate='cascade' would still need the record id.

If you keep the id then you are always referring to the record and any
updates of it.
This sounds more like a DB design issue.