Datasource integrity

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Datasource integrity

Post by crazycoders »

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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Datasource integrity

Post by josh »

You're trying to write a component driven CMS. Data template = "content type", data entity = "component", you got the "field" term right. Theres component CMSs, composite CMSs, and then there's schema based systems.

This book will teach you more. It comes in at 1,200 pages and it seems like the author wrote a program that automated writing a bunch of "filler" text, but there is a few gold nuggets between all the watery fill:
http://www.amazon.com/Content-Managemen ... 472&sr=8-1
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Datasource integrity

Post by crazycoders »

Well thank you, just too late, i asked this question last week or before that i don't remember and i have gotten to the conclusion that only object specific implementation would work. So i created the datasource object so that it manages the dependencies by itself using a table that i call a mapping table. This mechanism has been applied to all types of objects and consist of a table to references the object in a foreign key way, so the database will prevent deletion of the objects in case i forget something and if i implement it right it's a simple:

ObjectId int not null FK -> sourcetable
Ident Varchar(250) -> fktable

and Ident contains a generated SQL UPDATE statement that gets executed by the source object if something changes.

Then, the system has rules configured in the different scenarios. In the Datasource scenario, what i do is i delete all lines not currently used (using that mapping table) and then i import the new lines with a flag "NewLineState" and i apply the algorithm. If the algorithm is to find a replacement by value, i try to find it, update the mapping if i found it, if not, then i replace with NULL using the IDENT field which contains the "UPDATE ... SET field = ? WHERE id = xxx". Same mechanism apply to all other methods of handling datasource data. So this way, i does increase the database size, but my objects don't need to know who is referencing to them, they only know something is referencing to them and they update it using the ident sql.

What do you think?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Datasource integrity

Post by josh »

Sounds like a quick way to run into contention issues
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Datasource integrity

Post by crazycoders »

What do you mean exactly by contention. I didn't know this word in english so i looked it up in WikiPedia and came up with this:

# Resource contention, a general concept in communications and computing, is competition by users of a system for the facility at the same time:

* Contention (telecommunications), a channel access method
* The contention ratio, in computer networking, competition that applies specifically to the number of people connected to an ISP who share a set amount of bandwidth
* Lock contention, in computer science, where a mutual exclusion lock reduces the throughput by hindering the concurrency of a program
* Bus contention, in computer design, where multiple devices on a computer bus attempt to use it at the same time

So what did you mean by that?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Datasource integrity

Post by josh »

If you have 10 tables that can each handle 10 requests per second for example, and all tables have to go through table A ( an 11th "global index" table, or locking table ), Table A is going to have to foot 100 requests per second. Hit a table with enough traffic you could run into table locking / other contention related problems

What is the need this architecture is serving exactly?
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Datasource integrity

Post by crazycoders »

Won't happen since the software doesn't change data that often. The datasources stay static most of the time, i just needed a mechanism to handle potential changes. Usage of external datasources are already pretty rare. Over 800 users / 30 clients, we have 2 clients using external datasources and both are infact access databases that are used to store user information or inventory information that doesn't change often.

And scalability won't be a problem since the datasources are copied to the database for that precise reason, to prevent lockdown from external datasources that change too often. Locking while updating the datasource is obviously a problem but shouldnt happen very often. Not enough to bother actually.

The architecture this is serving is a SQL Server 2005 with Windows Desktop Applications. Most of our clients are hotshots such as goverments, hospitals and cities. So they have enough power in their servers to handle the contention issues you are talking about.
Post Reply