Jump to content

Prospects v Customers


This topic is 3058 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi,

I'm developing for a service company that has a telesales and a field sales team.  They then have a service team that visits customers and services products on site.

Question:

Both prospects and customers are currently in a table called 'companies' and have a simple flag (dropdown list) to indicate the difference.  Challenge is that the table has grown to 124 fields with some of the fields only relevant to either Prospects or Customers.  Remarkably I have never needed to create 1:1 relationships in such a scenario but I'm thinking of doing so.  Would it be advisable?  I'm thinking specifically it may lead to performance improvements on list layouts with some 20,000 prospect records being loaded?

Also having not used this technique before is there a way of having the related 1:1 records in the separated tables auto create when I create a record in the main company table?  I'm thinking this might be a really stupid question but I'm quite tired and just can't see it?

I look forward to your opinions.

Thank you,

Simon

Edited by Simon UK
Link to comment
Share on other sites

I would split this as contact being the main one with 1:1 relations to prospect, customer, and history only by contactID + the flag for the vlContactType,

and while you are at it use singular instead of plural forms of each defined table, use plural in scripts, and singular in the definition of the tables;

it will make the solution look more clear. After all a table only holds 1 piece of data pr record.

 

Perhaps into several if you want speed improvements

Contact( contactID, contactTypeID, contactName )

 

One to one for:

ContactProspect( contactID, contactTypeID, prospect relevant data )

ContactCustomer( contactID, contactTypeID, contact relevant data )

 

One to many for:

ContactMedia( contactID, contactMedia( such as email, address, phone, iPhone, android, viber, sip, VoIP, a s f, ) data )

 

If you want to go really crazy look at the sqlite database of Contact.app

Edited by ggt667
Link to comment
Share on other sites

Hi ggt667,

That has got me thinking!

I've set the 'Company' table as the main focus and have related tables for Contacts, Addresses etc with the ability for the user to choose the main Contact, main Address etc.  Whilst the client is talking to a Contact the main focus is doing business with the Company as contacts come and go.  I thought that it would be better to associate all work done, jobs, invoices etc with the Company primarily rather than the Contact?

Do you believe I should re-think this?

Simon

Edited by Simon UK
Link to comment
Share on other sites

All related entities that relate to the company should be linked to the company, not the contact.  You don't send an invoice to a contact, you send it to the company, it is the company that pays, not the contact.  You can certainly have an "attn of" on the invoice to link it to the relevant contact in the company. (Make sure to store that with the invoice including things like names, job titles, phone/fax/email, ... because these things will change over time and you want your invoice to reflect the point-in-time data).

As to prospect / client / supplier.  These are all "roles" that a company can have.  So ideally you'd have a "company_roles" table.  Each company can have many roles: it can be a client for one project, a prospect for another project and even a supplier at the same time...

  • Like 1
Link to comment
Share on other sites

Oh contact is person for you?

I have person together with title as each their text field in ContactMedia table.

Edited by ggt667
Link to comment
Share on other sites

This topic is 3058 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.