Database Design Help

35 pts.
Tags:
Database
Database design
Database optimization
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

in your DEAL table you will need to add
ORGANIZATION ID
LAWYER ID

Or optionally you might have another table with
DEAL ID
ORG ID
LAWYER ID
that shows the connections.

You might consider adding a sequence number to the DEAL ID in case the first lawyer drops out and you need a new lawyer to take his place. So now you would also want
LAWYER START DATE
LAWYER END DATE

That was a short answer. Data design must include all of the business rules.
Always remember the laws of normalization when laying out your data tables.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JessRomanowski
    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!
    35 pointsBadges:
    report
  • carlosdl
    I don't think you need a 'CurrentDealsID' (in fact I would use a different name for that table, something like 'Deals_Organizations'. So, the Deals_Organizations table would have at least:
    DealID
    OrganizationID
    LawyerID
    If you have a deal in which 3 organizations are involved, you would have 3 records in this table for that deal.
    DealID	OrganizationID	LawyerID
    1	100		25
    1	101		26
    1	102		27	
    Additional comment: Your design permits that any lawyer can represent any organization. Is that correct ?
    69,920 pointsBadges:
    report
  • CharlieBrowne
    Adding on to what Carlos has stated,I would add a start and end date in the Deals_Organizatrion file.It appears you also need to identify both organizations involved in the deal. Here is what your file could look like wiith multiple entries.
    1st Org         2nd Org  DealID	Lawyer ID	Start Date	End Date
    100	101	1	25	4/1/2010	
    100	102	1	25	4/1/2010	5/15/2010
    100	102	1	26	5/15/2010	
    100	150	2	26	4/18/2010	
    101	102	3	31	6/2/2010	
    105	107	4	19	5/12/2010	5/14/2010
    105	107	4	22	5/15/2010
    41,380 pointsBadges:
    report
  • JessRomanowski
    Thanks guys... Carlosdl: Yes, the assumption is that any lawyer can represent any organization. CharlieBrowne: I am not sure why I would need the start and end dates in the Deal_Organization table, should the start and end date fields be identified there rather than in the Deals table? Again.. Thank you thank you thank you!
    35 pointsBadges:
    report
  • DeepSpaceNine
    How would you ensure that an organization and/or lawyer is associated with a deal just once?
    30 pointsBadges:
    report
  • CharlieBrowne

    DeepSpaceNine

    Why are you asking?

    Is this a homework question for you?

    41,380 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following