Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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.
    • 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