SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict sdleihssirhc's Avatar
    Join Date
    Feb 2009
    Posts
    387
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    A Truly Unique Field

    Let's say I'm designing an employee database for a secret spy agency. This agency has all different kinds of employees (spies, janitors, middle managers, etc), all of which need to be in the db. Each kind of employee has its own fields, so each kind of employee needs to have its own table.

    For example: On the "spies" table, the fields might be "primary weapon," "current location," "kill count," etc. But for middle managers, the fields could be "department" and "budget."

    But ALL employees need to have a "panic phrase" that is unique to them (in case they're captured and can only get a single word out to the agency). I can't have a unique "panic phrase" field in each table, because that could allow for duplicate panic phrases in different tables.

    I could put all the panic phrases in their own table and just have a "panic_id" field in the employee tables, but then we have the same problem: two employees on different tables could point to the same panic phrase id.

    How do I design the database to make this work? Or is there no solution, other than checking each field in each table?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it's really simple, you will need a "supertype" table and several "subtype" tables

    you've used the term "employee" consistently, so let's call the supertype table the employees table

    their employee id would go in here as the PK, as well as any columns that they all share -- and panic phrase fits the bill perfectly

    then in each of the subtype tables, managers, spies, etc., they would have their individual custom columns, as well as their employee id as PK, but also simultaneously as FK to the employees table

    make sense?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict sdleihssirhc's Avatar
    Join Date
    Feb 2009
    Posts
    387
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    A ha! Of course. Thank you.


Bookmarks

Posting Permissions

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