Database Design Question

• djdeel

  SSC Rookie

  Points: 29

  More actions

  May 8, 2007 at 9:05 am

  #173578

  Hello,

  I have a problem where the business rules changed for my application. My application is basically a help desk type app. I have a main table that tracks the case information, eg:

  tblCap

  -------

  capID

  fk_customerID

  dateOpened

  dateClosed

  partner

  fk_buID

  etc...

  A case can now have multiple business units assigned to it and multiple business units can be considered primary (as in primary root cause). Eg:

  tblBU

  ------

  buID

  businessUnit

  What is the best method to now track multiple business units assigned to each case and further track whether that business unit is a primary failing business unit? Whether or not a business unit is primary just needs some kind of distinction, so I assume a bit flag can be used, just not sure how to set this up to make sense.

  Thanks for any help you can provide!

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 8, 2007 at 9:17 am

  #704963

  David

  Create a junction table that references the cases and the business units tables:

  tblBUCap

  ---------

  capID

  buID

  IsPrimary

  John

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  May 8, 2007 at 9:24 am

  #704969

  In addition

  Add an IDENTITY column (I'm sure I'll get flamed for this ) so that data can be tracked in insertion order (if required), or use a datetime column (or both!)

  Far away is close at hand in the images of elsewhere.
  Anon.