mORMot Open Source

migajek

Member

Registered: 2010-10-01

Posts: 89

RecordRef purpose, instead of ID in pivot table

Hi,
I don't really get the idea of keeping RecordReference which is a strange computed number, instead of simply record's ID in the pivot table?
What are advantages for internal design?
Cause for me it's only trouble, when I want to do some JOIN's in pure SQL I simply can't Should I keep record's ids on my own for that case?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: RecordRef purpose, instead of ID in pivot table

Putting a TSQLRecord property type will create a link to one record of the TSQLRecord type.

Putting a TRecordReference property type can link to ANY record of the database model.
The number is "strange", because it points to the record ID and to the record type itself.
This is very handy if you want to have one property able to link to diverse tables. A typical usage is in an Audit Trail, if you want to point to a record which has been modified.

Note that the data integrity is safe with this TRecordReference columns, for example in case of a deletion.

For the pivot table, it allows the pivot table to be used for multiple tables, with the same table name.

migajek

Member

Registered: 2010-10-01

Posts: 89

Re: RecordRef purpose, instead of ID in pivot table

Did I understood correctly, that in the case of RecordRef I can actually have one single table for all the possible combination of "connections" between records?
Is RecordRef kind of hash of record ID and record table (class type)?

Thanks,
m.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: RecordRef purpose, instead of ID in pivot table

Yes, it is.

See the RecordRef object, which is used to map the TRecordReference property content, to guess how it works.

It's not a "hash", it's just a bit-wise storage of the ID and the table index in the TSQLModel (that's why the order of tables at TSQLModel creation must remain the same during a database life: even if a table is deleted, it must always be present at the same place in the table list).