Help in Database Design

[arindam2013]

Hi All,

I am trying to create a database to store Clients & Contract information. Have tried all possible methods I am aware of to design the same :banghead:all these while.

Here is what I need in the database.
tblClients, tblContracts, tblServices, tblRate or Billing details, tblAccount Manager associated with the contracts, tblSales Manager who got the Contract.

1. Each Client may have multiple contracts.
2. Each Client may have multiple client location or cities falling under each contract.
3. Multiple Contact person for Each Client. One for sending invoice, one for sending reports, one or two for each location for coordinating an event etc.
4. Client Head Count has to be tracked for calculations for invoicing or reports.
5. Contracts may vary such as a contract might be a global one and other might be a local one.
6. Each contract will have a specific Account manager and sales manager associated with it.
7. Whereas Account manager or Sales manager might be associated with many contracts.
8. We might have received contracts thru a vendor, UK office, US Office, AU office china office etc.
9. Accordingly invoices has to be sent to appropriate vendor or offices to process. Same for Reports.
10. Services are customized one's for each client and hence may vary from client to client. there might be a limit for a service to one client for others the limit may be different.
11. Want to store promotional activities and events conducted in that client with location and other info.

Please let me know if this is possible. If anyone can help me. There are further more issues with what I have explained here. Thinking of going step by step. Any help on this is greatly appreciated.

 

[jzwp22]

If you generalize things a bit, it may help you with the table structure. Instead of having separate tables for account managers, sales managers and contacts, why not have 1 table for all people? Similarly, instead of having separate tables for clients and vendors, why not have 1 table for companies?

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
other fields pertinent to people

tblCompanies
-pkCompanyID primary key, autonumber
-txtCompanyName
other fields pertinent to companies


Since companies have many locations (headquarters, branches, offices etc.), that describes a one-to-many relationship, so we need a table for that

tblCompanyLocations
-pkCoLocID primary key, autonumber
-fkCompanyID foreign key to tblCompanies
-txtLocationName

Now since you can have many contact people at the various locations, we need a table for that

tblCompanyLocationPeople
-pkCoLocPeopleID primary key, autonumber
-fkCoLocID foreign key to tblCompanyLocations
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles (the role or title the person plays at the company location; you will need to add the role names to tblRoles as appropriate)

tblRoles
-pkRoleID primary key, autonumber
-txtRoleName

You would include your company, its locations and its people in the above tables as well.


Now before I get to the contract part, we have to understand more about the services you can provide to the customer. You said this:

10. Services are customized one's for each client and hence may vary from client to client. there might be a limit for a service to one client for others the limit may be different.

If a contract involves many customer locations, do the services vary by location as well?

 

[arindam2013]

Many thanks for your help on this friend,

If a contract involves many customer locations, do the services vary by location as well?

No the services doesnt vary by location of a single client. It only varies on a client to client basis.

Lets say we are delivering 5 different type of services as a package.
Serv1
Serv2
Serv3
Serv4
Serv5
Some clients may have only 3 in their contract and may not opt for other two. This is what I mean when I say that services vary.
Also there are some services which may have been started as a pilot and might not be in the package or for all clients.

 

[jzwp22]

OK, since the services do not vary by location that simplifies things somewhat. So let's start with the contracts. We'll need a basic table for the contract info

tblContracts
-pkContractID primary key, autonumber
-txtContractNo

Now, since multiple locations of a company are involved in a contract we have a one-to-many relationship. Further, since a company location can be involved in multiple contracts, we have another one-to-many relationship. As such we need a junction table.

tblContractCompanyLocations
-pkContractCoLocID primary key, autonumber
-fkContractID foreign key to tblContracts
-fkCoLocID foreign key to tblCompanyLocations

Just as a note, since a contract can involve multiple companies including your company, the client and in some cases a vendor, you will need to capture the locations of each in the above table. So for your company you might include your corporate location, a vendor office/branch location and then of course all of the client's locations covered by the contract.

Now, the people involved in the contract are related to each of the various locations tied to the contract. I assume that not all of the people of a company play a role in each contract, so we need to designate those who do

tblContractCompanyLocationPeople
-pkContractCoLocPeoID primary key, autonumber
-fkContractCoLocID foreign key to tblContractCompanyPeople
-fkCoLocPeopleID foreign key to tblCompanyLocationPeople
-fkRoleID foreign key to tblRole (you can use this to identify the role of a person at a company location relative to the contract if the role is different from their typical role. This would be an optional field).

OK, now the services, you will need a table to hold all services

tblServices
-pkServiceID primary key, autonumber
-txtService

Now, you have to related the particular services to the contract

tblContractServices
-pkContractServiceID primary key, autonumber
-fkContractID foreign key to tblContracts
-fkServiceID foreign key to tblServices


As a note, the data type of the foreign key fields must be long integer numbers to match the autonumber data type of the corresponding primary key field.

 

[arindam2013]

Thanks a ton...will work on it.

 

[jzwp22]

Please post back with any questions on the design.