Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    data modelling advice needed

    Hi

    Need advice on data modelling.

    I have a table called consignments which is identified by a cosignment_id .

    consignor ,consignee pickup and alertme are all types of roles parties can play within the system .
    i,e party can have many roles and roles can belong to many parties.

    A given consignment can have a consignor,consignee,pickup etc..

    How do i model the relationship between consignment and parties?

    regards
    Hrishy

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by db2hrishy
    How do i model the relationship between consignment and parties?
    Normally you'd just add a party_roles table and end up with the following tables :
    • consignments : consignment_id ...
    • parties : party_id ...
    • roles : role_name
    • party_roles : consignment_id, party_id, role_name

    This doesn't enforce any party roles are in place for a given consignment - you'd need to add a trigger or use a stored procedure to insert the data and ensure whatever roles that are needed are present. If only one party can have a given role on a consignment then it may be worth moving that entry into the consignments table but it could be argued either way.

    The roles table is just to provide a list of valid entries for the role_name being entered. Normally tables are named with a singular name ie consignment rather than consignments. Also it's often better to use capitalisation in table names (ie Consignment and PartyRole) just to make things a little clearer.

    Mike

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by db2hrishy
    Need advice on data modelling.
    Make sure you get with a reputable data modeling agency. Not just some sleazy project manner with a Polaroid working out of his basement.
    I was so naive....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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