Home > IT Answers > Database > Database Design Help
JessRomanowski
35 pts.
 Database Design Help
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.

Software/Hardware used:
ASKED: June 9, 2010  6:33 AM
UPDATED: June 9, 2010  9:44 PM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  June 9, 2010  2:22 pm  by  Meandyou   5,205 pts.
All Answer Wiki Contributors:  Meandyou   5,205 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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!

JessRomanowski
 35 pts.
 

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 ?

carlosdl
 63,535 pts.
 

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
CharlieBrowne
 32,855 pts.
 

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!

JessRomanowski
 35 pts.