knoops
Posts: 23
|
| Posted: 08/13/2007, 9:56 AM |
|
Hello everybody,
I have been wondering what the more experienced database-developers consider good practice when it comes to referential integrity between tables. There are a few ways to go about this as many may already know.
1. Hardcoding it into the database
This is probably the most solid and thus the most inflexible solution. MySQL for example offers the option to CASCADE a removal or an update by keeping separate tables, which keep track of all foreign keys.
2. Adjusting the DELETE and UPDATE in CodeCharge
I have been very cautious with this, because it seems like one could get lost in having all these custom queries all over your application. It works fine for very important records. For Customer records for example I have the DELETE to an SET active = 0. But this does not seem to me like a good option for simple lists, that can be adjusted
This is where I am very curious about how other Codecharge users have gone about maintaining the referential integrity in even the most simple database design. For example:
I have made an application for the workers in a youth centre, so that they can keep their reports available to each other. They organize events on different locations and have to select a location when they create a report. Over time the working locations may change, so they teamleaders wanted to be able to adjust the selectionlists on a different webpage. Pretty straightforward and quite common, right? So I made the list like this:
location : locationId, locationName, locationInfo
But what happens when they want to DELETE a location? Should I use the "deactivate"-method? Should I refrain from using IDs with lists like these and just use a textfield for any searches made? Should I give a warning that DELETING this location will remove all referenced reports?!
I can see the many pros and cons in the many options available, so I would like to know what decisions you have made in this dilemma between clean design and practical usability.
Thank you,
Knoops
|
 |
 |
E43509
Posts: 283
|
| Posted: 08/13/2007, 10:23 AM |
|
If you want to keep your application portable to other databases, manage all referential integrity in the application.
In nearly all cases for me, portablility is not a requirement.
That said ... I always put in primary key and foreign key constraints into my tables to keep the data clean.
Some db allow you to delete a parent row and cascade the delete for all the children. This kind of scares me too.
I make much more use of an 'obsolete' flag to hide data/values that are no longer used. (I like your deactivate method more)
I typically force them to delete the children and then delete the parent and not do it all in one statement
|
|
|
|
wkempees
|
| Posted: 08/13/2007, 12:17 PM |
|
(MyPersonal) reaction to:
Quote :
location : locationId, locationName, locationInfo
But what happens when they want to DELETE a location? Should I use the
"deactivate"-method? Should I refrain from using IDs with lists like these
and
just use a textfield for any searches made? Should I give a warning that
DELETING this location will remove all referenced reports?!
Flag it,
In selection lists do not present the flagged records.
In reports/search offer the flagged records, if historical data is needed.
Cascading is not an option, not in these kind of referal/lookup tables.
Like usertables, do we delete them, the users when they no longer
participate?
Nope.
If you do need cascading updates deletes, let the DB do the hard work and
depend on it's accuracy, we the builders tend to forget to adjust the
Update/Delete statements.
I come from a (corporate) background where application builders and Database
administrators used to be different breeds. They designed the databases we
built the applications.
They implemented all the rules and triggers as well as the stored procedures
and views.
We built the application on top.
DB's in general are made to be able to handle lots of data, it's what they
are good at.
So let them, just give them all the indexes they need to do it well.
Walter
|
|
|
|
|
