Well if departments are added and removed I would leave them in there and when you build your data entry form check if the "active_department" flag is set before listing the acceptable departments. That way you always have parent keys AND data entry errors while using external keys. Triggers add no advantage here. | [reply] |
Maybe if I explain what I am trying to do more. We get two flat files from another group. This group is difficult to deal with and lacks any techical people and won't add any flags to the data. One file contains the DEPARTMENT table data and the DEPARTMENT_CODE table contains the current valid DEPARTMENT_CODE values, so the actual data entry is done somewhere else and I am suppose to come up with a way to automate the loading of these flat files into the there corresponding tables. I am not allowed to delete any records from the DEPARTMENT table and I must replace the DEPARTMENT_CODE table with the data from the latestflat file.
| [reply] |
Let me rephase:
I have a database that is well-normalized. I have a group whose refusal to work with me is resulting in me being forced to ignore the normalization of my database and the potential error-catching. Thus, how do I neuter a well-normalized database?
You need to get your manager / director / CTO to lean very heavily on this other group and explain to them (using very small words) that their refusal to work within the defined process is counter-productive and will shoot them in the foot in the very near future.
Barring that, drop and reload the database every time you get a new file. If nothing else, it'll be a good goad on the group. (And, it'll feel amazing every time you know that group just lost data. *mhwahahaha*)
In other words, there is no good technical fix for people who refuse to follow the good technical solution that is already provided.
------
We are the carpenters and bricklayers of the Information Age.
The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6
... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms
Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.
| [reply] |
I am suppose to come up with a way to automate the loading of these flat files into the there corresponding tables. I am not allowed to delete any records from the DEPARTMENT table and I must replace the DEPARTMENT_CODE table with the data from the latest flat file.
It's that very last condition (must replace the DEPARTMENT_CODE table...) that seems most likely to cause trouble. In fact, it's likely to create unresolvable confusion when combined with the other condition in that sentence ("not allowed to delete any records from the DEPARTMENT table"). This is not a sustainable policy.
Now, if you are actually supposed to replace all the contents of both tables every time you get a pair of files from this other group, then things are not so bad -- but this implies that any DEPARTMENT entries present in the last file you got, but not present in the next file you get, will be deleted when you load that next file into the table (replacing the prior content).
So, first of all, make sure you have the right understanding of the policy. A full replacement of both tables each time you get a new pair of files can be manageable, and you should just go ahead and define those tables with the foreign key relation that you described initially. If, after loading the DEPARTMENT_CODE table, you find any records in the DEPARTMENT data that can't be inserted, you now just need to tell those jerks-- er, your colleagues in that other group that they must have made a mistake, because there are discrepancies between the two files.
On the other hand, accumulating more and more data into the DEPARTMENT table (never deleting anything from there), while replacing all of the DEPARTMENT_CODE table with each new file, is a disaster waiting to happen (e.g. when they decide to assign a previously used code number to some new department, the new department suddenly "inherits" a bunch of old table records).
| [reply] |