Prospects v Customers

[Simon UK]

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 October 16, 2015 by Simon UK

[ggt667]

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 October 16, 2015 by ggt667

[Simon UK]

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 October 16, 2015 by Simon UK

[Wim Decorte]

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...

[ggt667]

Oh contact is person for you?

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

Edited October 16, 2015 by ggt667

[Simon UK]

Hi,

That's been really helpful in clarifying my thinking, thank you both.

Simon