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

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

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....