Member Avatar for Jessie21

Organizations make business deals with other organizations. When negotiating a specific deal, each organization is represented by one lawyer. The same organization can have deals with many other organizations and it might use different lawyers in each case.
Each lawyer has a first and last name, address (Street, City, State, Zip), specialization and fee.
Each organization has a name, address (Street, City, State, Zip) and budget.
Each deal has a name, description, begin date and end date and might involve numerous organizations.

So where I'm so far is,

Tables:Lawyers
LawyerID
LawyerFName
LawyerLName
LawyerStreet
LawyerCity
LawyerState
LawyerZip
Specialization
Fee

Organizations
OranizationID
OrgName
OrgStreet
OrgCity
OrgState
OrgZip

Deals
DealID
DealDescription
BeginDate
EndDate

Ok, now I am not sure where to go from here... Obviously there has to be a relationships the Lawyers, the Organizations and the Deals, but I don't know how to make those relationships given that there are multiple organizations and multiple lawyers involved, yet only one table ID for each.... I'm confused.. Please help.

  • 3 Contributors
  • 4 Replies
  • 90 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by griswolf
Member Avatar for urtrivedi

and two forieng keys to your deal table
Lawyerid, organizationid
and one more say deal_fees

So you deal will be like
dealid, lawyerid,orgniszationid, deal_fees and other columns

Edited by urtrivedi because: n/a
Member Avatar for Jessie21

Ok so I've updated my tables:

Tables:

Lawyers
LawyerID
LawyerFName
LawyerLName
LawyerStreet
LawyerCity
LawyerState
LawyerZip
Specialization
Fee

Organizations
OrganizationID
OrgName
OrgStreet
OrgCity
OrgState
OrgZip

Deals
DealID
DealDescription
BeginDate
EndDate

CurrentDeals
CurrentDealsID
DealID
OrganizationID
LawyerID

BUT... I am still confused how I implement this properly since one of the assumed conditions is that each individual deal could consist of MANY organizations... More confused than ever :( Thanks everyone for helping!

Member Avatar for urtrivedi

what is difference between deal and current deal what is suggest for many oraganisation for one deal, the following solution, forget deal and currentdeal

deal_hdr (dealid, dealdescr,begindate,enddate)
deal_dtl (dealid (fk from deal_hdr),organiztionid,lawyerid)

here if there is only one lawyer for each deal then you may take layter to deal_hdr,
Now your one deal is having multiple organization.

Member Avatar for griswolf

You need two kinds of tables:

  1. informational (holds some kind of "raw" data)
  2. association (holds foreign keys and may have associated data)

you need info tables for: lawyer, deal, organization, as you have done

You also need an association table that has

  • lawyer_id foreign key,
  • organization_id foreign key,
  • deal_id foreign key
  • The unique key is all of the foreign keys

Each row tells you which lawyer works for which organization on which deal, and you can find the lawyers or organizations for a given deal by selecting on deal_id; the deals of an organization by selecting on organization_id, etc.

You don't need a current_deals table, since the begin and end dates are sufficient.

Edited by griswolf because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.