Planning New Database

[TheFonze73]

I would love some general feedback / advice on planning a database I am currently building e.g. Tables required - relationships etc.

The first thing I need to plan is how to allocate leads to salespeople.

It is for a small callcentre up to 12 users but may grow to 20+

We are running filemaker Server Advanced 9. (Could be upgraded to 11 if required)

Most users are running FMPRO10 or IWP at the moment

I have various seperate inbound and outbound campaigns.

I have a large number of leads / records.

I have multiple campaigns (specific product or service being sold)

I have multiple salespeople who could be exclusively selling on just one campaign (more likely) or selling on various campaigns (less likely).

I would like to be able to allocate leads to various campaigns. (I may need to allocate the same leads to multiple campaigns)

I would then like to allocate leads to various salespeople within certain campaigns.

I would like the salespeople to only have access to the campaigns and leads that have been allocated to them.

I would like the manager to have access to ALL campaigns and to be able to allocate leads.

I hope I have been clear. I would just like a general idea of how this could be done to see if I have planned this correctly.

I have a current database that I built but it only covers one campaign.

I will probably have to build a totally new dabase and just want to be sure I am heading in the right direction.

Many thanks

[TheFonze73]

HERE IS THE LATEST

I have a large number of leads / records.

I have multiple campaigns (specific product or service being sold).

I have multiple clients (people paying us to run campains)

I have multiple salespeople who could be exclusively selling on just one campaign (more likely) or selling on various campaigns (less likely).

I have Invoices in and invoices out

I Receive Payments from Clients.

I invoice clients (For campaigns)

I Generate Invoices Out for Salespeople

I allocate Clients to Campaigns

I allocate leads to various campaigns. (I may need to allocate the same leads to multiple campaigns)

I allocate leads to various sales people within cetain campaigns.

LEADS >------------------- CAMPAIGNS

CAMPAIGNS >------------------- CLIENTS

SALEPEOPLE >-----------------< CAMPAIGNS

SALEPEOPLE -------------------< INVOICES

CLIENTS -------------------< INVOICES

INVOICES >-----------------< PAYMENTS

PAYMENTS >------------------- CLIENTS

[TheFonze73]

THIS IS THE LATEST IDEA

I have a large number of leads / records.

I have multiple campaigns (specific product or service being sold).

I have multiple clients (people paying us to run campains).

I have multiple salespeople selling on one or more campaigns.

I have Invoices in and invoices out

I have payments in and payments out

I Receive Payments from Clients.

I invoice clients (For campaigns)

I Generate Invoices Out for Salespeople

I allocate Clients to Campaigns

I allocate leads to various campaigns. (I may need to allocate the same leads to multiple campaigns)

I allocate leads to various sales people within cetain campaigns.

[David Jondreau]

Will Leads be assignable to more than one sales person?

For Campaign - Sales People, you'll want a join table, let's call it Assignments. I think Leads would join with the Assignments table. Though you may want another join table between Assignments and Leads (Lead Assignments), if the answer to the question above is yes.

When you're facing a many-many relationship (a Sales Person may work on more than one Campaign, and a Campaign may have more than one Sales Person), you'll generally want to have a Join table.

I would probably have separate tables for Invoices to Clients and "Invoices" from Sales People.

[TheFonze73]

Hey D J

Thanks for the reply.

Yes leads could be allocated to multiple salespeople but only if they are working on different campaigns.

Is this what you had in mind?

The other issue I have is that the leads table will be holding a large number of records (minimum a few hundred thousand but prefferably 6 - 7 million).

I don't know how FM will perform with those numbers.

I was thinking that a seperate table for each campaign holding a copy of the leads allocated to it as the number of leads allocated to each campaign will obviously be a much smaller number.

How would I plan for this?

Thanks

Edited July 9, 2010 by Guest