JimmyCrackedCorn
Posts: 583
|
| Posted: 04/28/2008, 10:24 PM |
|
I'm using CCS with ASP and an Access database for many of our smaller projects. I'm wondering if anyone here has an opinion on whether it is better to let Access/JET engine manage referential integrity (at least in some cases) or should I always write my own code to detect (and prevent) orphaning of child records?
Basically if I set up relationships properly Access will manage referential integrity automatically. I can choose to allow or disallow cascading deletes and updates.
I'm just puzzling over whether best practice is to let the database do this work, write the code myself or does it vary by situation?
I'm also considering a suggestion someone here made (Walter I think?) that you don't delete records but instead mark them as inactive.
_________________
Walter Kempees...you are dearly missed. |
 |
 |
asaavedra
Posts: 15
|
| Posted: 04/29/2008, 6:38 AM |
|
Referential integrity and other database-driven support for business logic, such as stored procedures and triggers, are always a great benefit.
However, in heavy load scenarios (e. g. thousands of records per second, quite common in telco) such features can become a burden. Even simple indexing can become a bottleneck in such situations, and in those cases you may rely on very simple database structures (no indexing, no referential integrity, no triggers).
Otherwise, if your applications are way ahead of such data volume, I suggest always using referential integrity.
A. Saavedra
|
|
|
|
wkempees
|
| Posted: 05/01/2008, 5:44 AM |
|
Best practice:
Let the Database handle the rules and constraints that apply to the
BusinessLogic at hand.
It is what it was build for (Oracle, MySQL, PostgreSQL, MSSQL).
Old school would imply a DatabaseAdministrator be responsible for database
design and management, thereby designing the database schema in such way
that even direct CLI access would follow the applied rules.
Meaning, when accessing the database directly through CLI
(CommandLineInterface) it would be implossible to do anything to the data
that would render it invalid, create orphan rows etc.
IMO, I would apply update and delete constraints (cascading updates/deletes)
saving me the burdon of everytime reaplying the code to cater for these, or
use stored procedures (in fact the same).
As to the 'mark for deletion' remark, one could apply this strategy to
(parent) tables.
In CCS it would meant to filter (Grids/records/reports) for the 'not marked'
rows and to have (Administrator Panel) a job that would handle the definite
delete jobs, selecting all the 'marked for deletion' rows and their
children.
Walter
|
|
|
|
DaveSause
Posts: 14
|
| Posted: 05/01/2008, 6:02 AM |
|
In my humble opinion, setting defaults, validation rules and referential integrity seems to be a database function, if you code it into CCS, then you have to replicate it with every web page instance; if in the database, it follows the data. I'm actually back in CodeCharge not CCS, so there are times when code can't perform the kind of validation I need, or something that looks correct on screen is not quite pure enough for Access - so I have to patch up what CC will live with to get Access to take it. Cascade update is useful where you want to let the user maintenance the key fields. Cascade delete is helpful - but in many cases you merely want to hide the records rather than deleting them completely from parent/child tables. How much power do you want your users to have? Frequently, I just set a true/false field for activity and let them maintenance that. Of course, once they set it inactive, then they can't get to the record to undo it, they must have direct database access, or perhaps certain users can through secured pages created for that sole purpose.
_________________
Dave Sause |
|
|
|
|
