OK, second attempt at the question (first is How to build virtual columns?)
Apologies in advance if this kind of question isn't suitable fo StackOverflow. Feel free to take it down if needed.
The basic question is "what's the best way to have a column whose content is built dynamically".
The code revolves around four tables.
First three (equipment, accessory, association) can be seen as two colums each, an ID and a name.
The goal is to replace the association name with a name built dynamically based on the name of the association components.
The fourth table describes the associations. The association should be seen as a tree, and each "branch" of the tree is represented as a line in this table. Columns are:
branchID (primary key)
association ID (int)
parent node kind (association = 1, equipement = 2, accessory = 3) (int)
parent node ID (ID in one of the three other tables) (int)
kid node kind
kid node ID
I do have something that works, using a view and a function (the function code follows). However, performance isn't satisfactory.
I see three improvement path:
minor adjustments through primary keys and indexes (code is significantly faster if there is NO primary key on the 4th table - I haven't been able to explain that)
fully reviewing the design behind the 4th table (I'm open to ideas)
replacing the custom function below by... something else! But what could that be?
Sorry for the French names... I chose not to edit the code before posting, assuming that copy/paste errors are worse than translation
Type = kind
Enfant = kid
Jumelage = association
Numero = name (oops...)
liens = branches
Thanks.