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).

In reply to Re^4: (OT) database design question by graff
in thread (OT) database design question by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.