use Perl

• Which database engine are you using? (Score:1)

  by mattk (7945)

  You could use Postgres schemas to achieve this - keep your survey table in one schema, and your faculty/department tables, identically structured, in seperate schemas. When you want to work with the faculty stuff, set your search path to survey + faculty. If the survey schema holds unique keys, the same join queries should return different results based on which schema is active.
  • Here's what I'd try first (Score:1)

    by Ron Savage (5224) on 2008.06.14 20:37 (#63410) Journal

    o The survey table has a context_id
    
    o The context table has:
    - the id pointed to by context_id
    - the owner_id
    - the owner_type_id
    
    o The owner_type table has:
    - the id pointed to by owner_type_id
    - the name of the owner type
    
    o The owner_id points to either the faculty
    table's id or the dept table's id (if you
    insist on having these in separate tables,
    an assumption which needs questioning)
    
    (o Alternately, the faculty and dept tables
    can have owner_type_id, of course)
    
    o The owner_type's name tells you what type
    of thing, faculty or dept, to instantiate
    to handle the desired behaviour of the
    owner
    
    o The owner_type table can be effortlessly
    extended, as per the very last comment in
    your original post
    
    o The golden rule is that every list of
    things goes in it's own table. Hence the
    tables for surveys, owner_types, faculties
    and depts (if the last 2 are really so
    different)
    
    o From that follows the owner_type_id
    field and the owner_id field
    
    o Your design fault, which you seem committed
    to, is the put a list into a list of foreign
    keys, faculty_id and dept_id, rather than
    into a table. It's a classic mistake :-)
    
    o Reposted via emacs after use.$curse.perl.org
    let me preview and submit my last post but still
    threw it away, AFAICT
    

    Reply to This

    Parent