Datasource integrity
Posted: Thu Nov 27, 2008 3:43 pm
Hi, this is a topic that is not necessarily related to PHP but more to DataSource management.
I have a software that manages cases, configuration items (inventory) and contact informations. This software allows the administrator to configure templates for each of these types of information and these templates have fields that the administrator can customize.
These fields can be configured to display information in a listbox and the information in that listbox can be edited by the administrator. So, to all speak the same term, let's call these datasources.
Datasources offer a value/label pair information list. They can be used to store data localy and provided by the administrator or they can fetch data from an external datasource. (In the case of my software, it uses OLEDB providers through ADO, which provides a good deal of flexibility) The information entered into this datasource can then be used. In my current DB and in most database, i'm not sure if it would be possible (due to the templating system) to integrate a correct referencial integrity system, thus all integrity must be handled by the software.
If the users were to use one of the values of the datasource it would be stored in another table in one of the custom fields. To preserve integrity, i'd have a plethora of ways to lock the value in the datasource but my problem goes farther than that. What if the administrator wanted to actually remove a value from a datasource and this value was used? Then possible scenario i'm tempted to use is the following:
Implement rules into the datasource object that checks of dependant information and if the value being changed is used, then it has to be replaced following the integrity rule which more than often would simply be to erase/replace the value used in another table (same principle as ON UPDATE SET NULL or ON UPDATE CASCADE)
Another problem to face is that the multiple tables existing in the system are handled through an Object Based API that tries to be as transparent as possible. If i start adding code to the datasource object that relates to other tables he should not be aware of, i'm scared it may create further problems later.
Please tell me what you think and how you'd actually solve this problem.
Thanks
I have a software that manages cases, configuration items (inventory) and contact informations. This software allows the administrator to configure templates for each of these types of information and these templates have fields that the administrator can customize.
These fields can be configured to display information in a listbox and the information in that listbox can be edited by the administrator. So, to all speak the same term, let's call these datasources.
Datasources offer a value/label pair information list. They can be used to store data localy and provided by the administrator or they can fetch data from an external datasource. (In the case of my software, it uses OLEDB providers through ADO, which provides a good deal of flexibility) The information entered into this datasource can then be used. In my current DB and in most database, i'm not sure if it would be possible (due to the templating system) to integrate a correct referencial integrity system, thus all integrity must be handled by the software.
If the users were to use one of the values of the datasource it would be stored in another table in one of the custom fields. To preserve integrity, i'd have a plethora of ways to lock the value in the datasource but my problem goes farther than that. What if the administrator wanted to actually remove a value from a datasource and this value was used? Then possible scenario i'm tempted to use is the following:
Implement rules into the datasource object that checks of dependant information and if the value being changed is used, then it has to be replaced following the integrity rule which more than often would simply be to erase/replace the value used in another table (same principle as ON UPDATE SET NULL or ON UPDATE CASCADE)
Another problem to face is that the multiple tables existing in the system are handled through an Object Based API that tries to be as transparent as possible. If i start adding code to the datasource object that relates to other tables he should not be aware of, i'm scared it may create further problems later.
Please tell me what you think and how you'd actually solve this problem.
Thanks