I've got a database I am focusing on the look for. I've producers and that i have marketers on separate tables that contains practically exactly the same information with couple of exceptions. Both groups have one-many contacts that should be attached to them. I produced a message table to carry contact details, one!
Will I require a second contact table? I am attempting to make this as DRY as you possibly can. Wouldso would that appear to be? Thanks ahead of time
Perhaps a situation for that party-role pattern?
Distributor are roles performed by Parties. Contacts affect Parties, not the role(s) they play. So you'd have:
- a table named
- a table named
- single:Michael relationship from Party to ContactMethod
which may resolve the requirement for two
Contact tables. The way you model the roles side will rely on wider needs. The canonical model might have:
- just one supertype named
- a M:M relationship from Party to Role
- a subtype of Role for every specific role (Distributor and Manufacturer inside your situation).
(Note: being an aside, this enables a celebration to experience both manufacturer and distributor roles - which might be relevant).
You will find 3 'standard' designs for applying a subtype hierarchy in relational tables:
- table for entire hierarchy
- table per leaf subtype
- table per type
(1) would apply without having any role-specific associations. (However suspect that's unlikely there's most likely information associated with Marketers that does not affect Producers and vice-versa).
(2) means multiple associations from Party (i.e. someone to each role subtype).
(3) eliminates both above but means an additional participate in moving from Party to the role(s).
Like I only say, choice is dependent on wider reqs.