Multiple Databases, ONE MASTER RECORD

[the wheelhorse]

Hey everyone, again thanks for all the help in the past!!!

So I have a silly problem.

We have a database that serves the web via CWP (xml/xlst). There are two "types" of records, a "PROSPECT" and a "ACCOUNT", based on these two choices in a single field , "type".

All of these records have previously been reviewed and edited for accuracy and duplication.

We want to create another database to use to import large lists of information via excel, etc. "LEADS"

The issue is, I want to create a database for sales people so they can see a record from EITHER table/DB in one layout. (There will be assymetrical loading of records in each table, so difficult to use serial # to link them)

So it sounds like I need to create a third MASTER join table that is where the initial K_ID is defined (and record imported?)

Related to this issues is the idea of breaking the records out of the tables into multiple tables segmented by physical region of the country so each sales person basically has his own table, so can't damage or see other people's data. YET we would want to be able to see each of these in one master table.

So the second part can be solved by a good solution in the first part.

Please excuse me if this seems easy and I am a bone head. I have no problem setting the relationships and key fields if there was a linear progression of data from one database to the other, but there won't be.

New info will constantly be dropped into the "LEADS" database (unqualified data) and there will always be "PROSPECTS" (qualified data) and we want to be able to work with either/or seemlessly. (as well as 'push' them into an "ACCOUNTS" db that is live online.

hmmm. make sense.

Any guidance, directions, resources to sites to look at would be helpful. I am the lone FMP guy at our company and people expect me to be able to do anything. yikes.

[T-Square]

It seems to me that in both cases you might best be served by having all the data in one table, with additional fields or values that allow you to isolate subsections of the dataset.

In the first instance, imported leads can be pulled directly into the same table, with the Type set to LEAD. You script or build self-join relationships that limit your sales staff to see only the types that they need to see. Putting all your eggs in this single basket also makes it possible for you to change a lead to a prospect to an account without cranky scripting later.

Similarly, keep all contacts in one table, create a Regions table, and either:

1) add a Region field to the contact table, in the case where a Lead/Prospect/Account has only one region, or

2) add a ContactRegion join table, in the case where a contact can have more than one region.

HTH,

David