Tables & relationships (1 Viewer)

[Richardr]

Hi there,

I'm new to database development and was hoping that someone might be kind enough to tell me if I'm on the right track with my tables - in terms of how they are made up / linked together?

A relationship report is attached to show what I've done thus far.

I work for a small management consulting firm and I'm attempting to build a CRM system to track our clients, campaigns and the sales pipeline. In essence, as briefly as possible, I'm ideally wanting the database to:

1) Store details about our clients [company] and the contacts within each company [contact] including the company's industry (e.g. telecoms) and the contact's interest (e.g. database contract management).

2) I then want to record a marketing initiative [campaign] such as a seminar and identify the event's focus (eg "contract management in the telecoms sector) and note the contacts who attended.

3) The next stage would be to ID those who represent a business opportunity [lead] and then, when deemed appropriate, submit a [proposal]. If the proposal is accepted, it then becomes a [project] which would be worked on by consultants.

4) Finally, the [sales] would show the details of the fees charged, for different elements of the project, delivered by the different consultants.

Any help or advice would be gratefully received. Please let me know if you need any further info.

 

[The_Doc_Man]

When I tried to read your document, it blurred on me and my tired old eyes didn't do so well. Let me take an approach that might show you how I would begin, and if it looks like what you are doing, then you have positive feedback. You might have to look up certain terms; I will italicize any term we use commonly here as a quick way to define our jargon.

1) Store details about our clients [company] and the contacts within each company [contact] including the company's industry (e.g. telecoms) and the contact's interest (e.g. database contract management).

Implies a COMPANY table and a CONTACTS table, for which INDUSTRY is a field within COMPANY and INTEREST is a field within CONTACT. POSSIBLY implies a list of choices for these fields if they are both supposed to overlap, meaning one more table listing possible topics for those fields. If they don't overlap, then TWO possible topic tables. Depending on how these things would be input, they might drive a combo box to store a code in the tables that would link back to the topics. That would be your choice.

2) I then want to record a marketing initiative [campaign] such as a seminar and identify the event's focus (eg "contract management in the telecoms sector) and note the contacts who attended.

Implies a CAMPAIGN table and an EVENT table, plus a junction table between the CONTACT and EVENT tables.

3) The next stage would be to ID those who represent a business opportunity [lead] and then, when deemed appropriate, submit a [proposal]. If the proposal is accepted, it then becomes a [project] which would be worked on by consultants.

Implies a LEADS table and a PROPOSALS table, a PROJECTS table, and a CONSULTANTS table. From what I see, there would be a parent/child relationship between LEADS and PROPOSALS since one could imagine more than one proposal deriving from a single lead. If the proposal becomes a project, then you have a parent/child relation between PROPOSAL and PROJECT, and I'm going to guess multiple consultants could be working on multiple projects at once. Therefore, you have another likely junction table between PROJECT and CONSULTANT.

4) Finally, the [sales] would show the details of the fees charged, for different elements of the project, delivered by the different consultants.

This implies a SALES ELEMENT table that is a child of the PROJECT. How the CONSULTANTS fit in depends on whether each one works independently AND SINGLY on an element. There would either be a parent/child or a junction table between SALES ELEMENT and CONSULTANT.

This is how I might approach the problem you described in English. Hope it helps.

 

[CJ_London]

question also asked here

http://www.accessforums.net/showthread.php?t=62560&p=335638#post335638

Doesn't look like OP has taken onboard the advice given so far.

@Richard - if you are going to cross post, please do so with consideration for the people giving their time freely - this link explains why/how

http://www.excelguru.ca/content.php?184