# Better References fields
**URL:** https://discuss.tryton.org/t/better-references-fields/177
**Category:** Feature
**Created:** [August 3, 2016, 10:16am UTC](https://discuss.tryton.org/t/better-references-fields/177 "2016-08-03T10:16:00Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@JCavallo](https://discuss.tryton.org/u/JCavallo)
#### Post date: [August 3, 2016, 10:16am UTC](https://discuss.tryton.org/t/better-references-fields/177/1 "2016-08-03T10:16:00Z")
## Rationale
Reference fields are currently hard to deal with when writing SQL queries, since joins require either Concatenations or Substrings, which is neither easy to read nor efficient.
## Solution
Add a way to store reference fields in two split columns, one for the model, and one for the id.
Advantages :
- Easier to join on
- Reduce index size =\> Usually, an index on the “id” part will be enough
## Implementation
I see two solutions for this :
- Add a new field type. I do not know if it is possible to this properly since fields are bound to either one or no columns, and this one should be bound to two.
- Add a way to declare a function field and the two column fields in one go : [Example implementation](http://pastebin.com/HtcHH6wR)
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 6, 2016, 2:45pm UTC](https://discuss.tryton.org/t/better-references-fields/177/2 "2016-08-06T14:45:52Z")
The only valid rationale for me is the efficiency of join. It is valid because there is no index available on the ID part. But indeed, I’m wondering if adding an index on the substring expression will not be enough.
I also have concern about loosing the one to one relation between a field and a column because there will be risk to have column name collision (via modularity).
---
### Author:  [@JCavallo](https://discuss.tryton.org/u/JCavallo)
#### Post date: [August 8, 2016, 7:13am UTC](https://discuss.tryton.org/t/better-references-fields/177/3 "2016-08-08T07:13:57Z")
Just to clarify, “Easier to join on” was meant mainly in terms of readability.
For efficiency, I usually go with concatenating the target substring and the joined id to then match the full string, which allows to use both the target id index and the reference field index.
Also, the index size actually matters when updating large tables, since indexes on reference fields (20+ char) are significantely bigger than they are on IDs.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 8, 2016, 10:20am UTC](https://discuss.tryton.org/t/better-references-fields/177/4 "2016-08-08T10:20:02Z")
> [@JCavallo](#):
>
> Just to clarify, “Easier to join on” was meant mainly in terms of readability.
For readability, we could create a helper.
> [@JCavallo](#):
>
> For efficiency, I usually go with concatenating the target substring and the joined id to then match the full string, which allows to use both the target id index and the reference field index.
This does not scale.
> [@JCavallo](#):
>
> Also, the index size actually matters when updating large tables, since indexes on reference fields (20+ char) are significantely bigger than they are on IDs.
As you will have to index also the model string, it does not matter. More over my suggested index is a index on integers.
---
### Author:  [@JCavallo](https://discuss.tryton.org/u/JCavallo)
#### Post date: [August 8, 2016, 11:04am UTC](https://discuss.tryton.org/t/better-references-fields/177/5 "2016-08-08T11:04:06Z")
[quote="ced, post:4, topic:177]
> [@JCavallo](#):
>
> Just to clarify, “Easier to join on” was meant mainly in terms of readability.
For readability, we could create a helper.
[/quote]
What kind of helper are you thinking of ? A custom sql operator ?
[quote="ced, post:4, topic:177]
> [@JCavallo](#):
>
> For efficiency, I usually go with concatenating the target substring and the joined id to then match the full string, which allows to use both the target id index and the reference field index.
This does not scale.
[/quote]
I feared it would not, that’s one of the reason for which I would like to improve it !
[quote="ced, post:4, topic:177]
> [@JCavallo](#):
>
> Also, the index size actually matters when updating large tables, since indexes on reference fields (20+ char) are significantely bigger than they are on IDs.
As you will have to index also the model string, it does not matter. More over my suggested index is a index on integers.
[/quote]
I am not sure that indexing on the model string will always be necessary. I mean, usually there are only a few effective models, and I think there will almost never be searches only on the model part. So a simple index on the id part should be enough for most cases.
Also, I do not think that the index you proposed would be used for this particular kind of searches (`('origin', 'like', 'account.move,%')`)
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 8, 2016, 3:16pm UTC](https://discuss.tryton.org/t/better-references-fields/177/6 "2016-08-08T15:16:24Z")
> [@JCavallo](#):
>
> What kind of helper are you thinking of ? A custom sql operator ?
I do not know but we do often the same thing so we can create an helper.
> [@JCavallo](#):
>
> I think there will almost never be searches only on the model part. So a simple index on the id part should be enough for most cases.
I’m not sure you know how indexes are used by database. Of course it is possible to create an composite index on the reference field that will be used for expression used on Reference.convert\_domain.
---
### Author:  [@JCavallo](https://discuss.tryton.org/u/JCavallo)
#### Post date: [August 9, 2016, 7:10am UTC](https://discuss.tryton.org/t/better-references-fields/177/7 "2016-08-09T07:10:18Z")
I played around with the prototype I posted in the opening post, and I noticed that the model part of the reference fields is very expensive (in terms of DB volume). This is logical, but I did not thought it would be that much.
Anyway, is there a chance to support postgresql’s ENUM type someday ? I think it might fit the Selection / Reference model use case at least in some cases.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [October 11, 2022, 8:25am UTC](https://discuss.tryton.org/t/better-references-fields/177/8 "2022-10-11T08:25:03Z")
With [Index ID part of Reference field (#11788) · Issues · Tryton / Tryton · GitLab](https://bugs.tryton.org/issue11788) this proposal should no more be needed.