Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009

    Question What kinds of relationships to use?

    I have a database with two related tables: tblPersonnel (One) and tblPositions (Many). The problem is this: I want to add information about a person's qualifications for the position to the database. I can't add it to the Personnel table, because it depends on what position they fill, and I can't add it to the positions table, because it doesn't update consistantly (If you remove a person from the position, the qualification data stays...)

    What I want is when you delete a personnel record or remove a person from a position, the qualification data will evaporate. (And maybe be reinserted by some code, but I would rather have empty data that needs to be updated then wrong data that is just leftover from earlier changes.) Is there any way to do this in the database, or do I have to do it with triggered code?
    Last edited by RetraRoyale; 05-29-09 at 11:45.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    the persons qualifications are an attribute of the person, not the position they hold. there may be a constraint that says anyone in position XYZ must be educated to undergraduate level, but that is a function of the position, as presumably someone educated to postgraduate level also qualifies for that position.
    I would have expected a FK in the personnel table to point to either your positions table, or an intersection table which associates a person with a position type
    position type could identify say porjcet managers, developers, dba's, qc, front line support etc. and there is an iontermediater tables which associates a specific person with a specific role

    however it may be that postions is an inersection table (recording the fact that, say, RetraRoyale is a developer, appointed on x date

    its relatively rare to come accross a genuine many to many table design (at logical level yes, but not at physical level.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Well, I was partially thinking like that. I have another table to keep track of a person's education and training, but I can't really constrain the data in a way that's compatable with the position table. (That is, the education and training data is to be treated as independent of the qualification for the position. Also, education information is kept for a different reason.)

    We will have to be able to assign someone as qualified for a position, even if we have no record of their education and training. I just need to be able to justify it outside of their education data. Also, I'm not thinking of it as "a person holds these qualifications (or qualifications for these certain positions)" because the positions are too flexible and unique. Actually, positions are records in the position table, which draws data from multiple sources (Document, department, etc...)

    Actually, it might be better to think of qualification data as temporary. That's why I need it to go away easily.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts