Guidance on linking databases

[Blitznb]

Hello,

I am looking for some guidance/suggestions on best way to manage a design with Access 2007-2010.

I have two db's both in sharepoint if that matters.

1. Customers with orders, linked by customer number
A-For customers there are contacts which also are linked by customer number
2. Leads
A-Contacts for the leads that are linked by ID of Leads

what is the best way to get both Customers and leads "together" for a "contacts" list db? Basically if a lead DOES NOT have a CustomerID then it would sync/link with Leads db. If there was a CustomerID it would sync/link to Customers DB. If this is possible would it also be possible to say add a customerID to leads table and it update into Customer DB?


Currently I have "imported" customers into Leads DB, but this is trouble as I cant update when customers are added to customers......back into leads or vise versa. The purpose of this was to use it as a contact db as well as track calls and emails......

Is this even the suggested method to approach?

 

[Mihail]

I don't understand very well your DBs, so forgive me if I am wrong.

For me, to be a CUSTOMER or a LEAD are properties to the same person.
So, a single table appear as enough:
tblPersons
ID_Person - AutoNumber (PK)
IsCustomer - Yes/No
IsLead - Yes/No

 

[Blitznb]

Thank you for your reply Mihail,


But I would prefer to keep them separate. I have roughly 10,000 leads and 1000 customers. I would prefer to keep the Leads out of the "Sales" database unless they end up with a "order"

 

[bob fitz]

Thank you for your reply Mihail,


But I would prefer to keep them separate. I have roughly 10,000 leads and 1000 customers. I would prefer to keep the Leads out of the "Sales" database unless they end up with a "order"

I'm afraid I'm with Mihail on this one. Would be best to use one table with a field to denote status(Customer or Lead).
Why would you prefer to keep them separately.

 

[jdraw]

How long do you keep a "lead" in your database? That is, when does a Lead cease to be a Lead? I understand that if they create an Order, they become a Customer. But what about the ones that go to "never-never" land and just disappear?

What info do you keep for a Lead that you do not keep for Customer?

 

[Blitznb]

Well maybe the better question is, what is the suggested method to link/sync those "leads" to customer table? But automate it so I dont have to update the information in both leads and customers.

I currently have a customer database, it contains, suppliers, orders and sub contacts for each. From this DB I have linked customers table to Leads DB. The real issue is how to "sync" customerstable with leadstable, i guess.

As a way to track emails, contacts & calls I brought a linked version of "Customers Table" into my leads table and imported them to the "leadstable". However this creates a disconnect when the need to update a customer comes about or a lead is converted to a customer. It would have to be done in both.

 

[Blitznb]

How long do you keep a "lead" in your database? That is, when does a Lead cease to be a Lead? I understand that if they create an Order, they become a Customer. But what about the ones that go to "never-never" land and just disappear?

What info do you keep for a Lead that you do not keep for Customer?

For infinity truly or until they close...... then they are changed to inactive. I track, calls, appointments, emails, SIC Codes and business details.

Nothing that is different from a customer. They were started in different dbs as I inherited the customer one and it currently has over 30k orders. I feared size would be an issue.

The leads was started for standard reasons and I also use the "combined version" (Leads and customers) to provide Email and Cell Phone contacts

 

[Blitznb]

Ok, how about suggestions on this. Whats best approach to sync rows from leads table that contain CustomerID to Customer table (Sharepoint Linked) in the same database?

Would this suffice?

 

[jdraw]

I don't deal with Sharepoint and really have no knowledge of it.

Do all Customers have Orders (1 or more).... is that your definition of Customer?

If you can allow a Customer to have 0 or more Orders, then you could have an attribute that identifies IsStillALead Y/N. Customers with 0 Orders are Leads. Leads do not have related Orders in your database.
The implication/consequence is that when you do any queries using Customer table, you must include the IsStillALead field in your criteria to ensure the field is N when dealing with Customers, and it must by Y when dealing with Leads.

If Customer s must have 1 or more Orders then following comments apply.

You could review a data modelling approach called Generalization. That would allow you to have a supertype "Person" table that has subtypes "Customers" and "Leads". Those attributes which are common are held in the Person table. The attributes specific to a subtype are stored with that subtype table.

There is a brief writeup here
http://www.learndatamodeling.com/dm_super_type.php#.Un7bKyewWvM

There is a tutorial (slides) here
http://sd271.k12.id.us/lchs/faculty/bkeylon/oracle/database_design/section4/dd_s04_l01.pdf

There is a more in depth youtube video by NancyIT2009 at
https://www.youtube.com/watch?v=ZTPAMJ9MzdY

Good luck.

 

[Blitznb]

I just looked any my leads db has 19479 unique records. The customer db has 901 "customers" and these customers have over 30000 orders.

I fear mixing leads and customers into a single table would create multiple issues syncing/link through sharepoint. I also fear great issues with the orders being queried against 20k + customer/lead combos.


It would be fine to combine the customers into leads table as long as those customers would sync back to the customer table.

Basically what I am looking for is the best method to sync table 1 with table 2 (where a certain field is not null/empty)

How do you handle when you have to split a table because there is too much data in it? Is that not basically what Im asking? Does it have to be moved to SQL?

Or could a table be created that syncs leads with leads where CID is null/empty and syncs customer with customers when CID is not null?


Fear im not being clear or maybe I just cant think in db terms.

 

[jdraw]

20000 or 30000 records in tables is definitely NOT TOO Many. I am speaking from Access perspective --- I don't know limitations of Sharepoint.

Of your 19479 currently stored Leads, how many are less than 6 months old? How many older than 1 year?

 

[Blitznb]

Thank you for your replies jdraw,

Im afraid I can not convince myself to enter 20k leads into my orders db. The issues in adding orders manually to me just outweigh the singular benefit.

To me, it seems like I am looking for an automated version of update/append/or insert (query) - that goes both ways and I can exclude some records.

Again I think you for your suggestions.

 

[spikepl]

@Blitznb

You seem completely stuck in your perception that 20,000 of anything is a lot. For a properly indexed DB 20,000 records is NOTHING. It has been suggested to you time and over again to put the two together, and yet you persist in your search for some other holy grail. Take a copy of your DB, suspend your disbelief, put the two together, index the table properly, and sit back and be amazed

 

[Blitznb]

Thank you for your replay spikepl, but yes 20,000 is alot to me when you are adding orders to those 900 among 20,900. Esp when its not a computer savy person adding the orders.

I am sorry that it makes more sense to me, to address only the two tables as opposed to changing the entire structure of two databases and subsequent queries, forms and ........

Ill just creat a without matching querry and change it to append and manually update every so often......I suppose.

 

[spikepl]

Again try to suspend your disbelief just for a monent (=i.e. stop being pigheaded for about 60 seconds - the time to read the remainder of this)

DO not mix data storage with how it is displayed to the user. These two are two entirely different things

The source of your confusion could be the idea of a table. In fact the user should never ever be allowed to access a table directly. Data is extracted from tables using queries and shown in appropriate forms. In this manner the user wil never ever see that the data is all stored in the same table.

The advantage of storing boht your cusotmers and leads is that the data is normalized (= hunky-dory). Denormalized data is allowed, but the built-in penalty for denormalized data is eternal pain when modifying and updating anything.

 

[Blitznb]

Ok thanks for the comments, i think.

Yes I guess Im pigheaded because I dont want to introduce 20000 records to my customers database, when 99.9% of them will never be needed in that db. Its seems ridiculous to combine both tables, re-key, re-connect to sharepoint, check/change all queries, forms and reports............. Not to mention insure the leads db now conforms to web requirements.

I believe the mistake was made when I added this to Design section as all already exists