simple
use RAS replication to pass data between the locations
use customerID + Location ID as the primary key (preferred) or set the
generators spaced apart so the IDs don't clash
(depends on the numbe of locations)
the first method will allow you to search for customerID where locid is any
other locid than current but still give you unique customers per location
How are the dbs "sent to each other" pity you don't have live connection
then replication could be hourly and still not allow locid 1 to use record
from locid 2 unless searching for other locid customerIDs

I do this now on 4 locations - works well

Alan
-----Original Message-----
From: Joe Martinez [mailto:joe@...]
Sent: Saturday, 24 August 2002 16:09
To: Interbase Support
Subject: [ib-support] Handling multiple locations


I'm sorry if this is a bit off topic. It's more of a general database
question, not specific to IB/FB, but there are a lot of great DB people
here, so hopefully someone knows what I should do here.

I have a database with customer records, as well as other records, such
as sales, etc. that link to the customer record in a
1(customer):many(sales) relationship.

Now, there are several locations owned by the same business. They do
not have live connections to each others' databases. The different
locations' databases are sent to each other nightly, and kept separate.
We don't want each location's customer record populated to every other
location. We don't want the same customer in more than one location's
database unless they've actually done business at both locations.

If a customer comes to Location A, and is not found in that location's
database, the application will search each of the other locations'
databases to see if the customer is found there (searching by phone #,
name, etc). If that customer is found in, say, Location D's database,
it will be copied (without the other detail records) to Location A's
database.

The customer table has a key field (customerID) which is generated
automatically (sequentially) when the customer is added.

Goal: If a customer comes into a location, we want to be able to see
their sales history for all locations. We will do this by querying each
of the other locations' databases for that same customer, and showing
all of the sales records in one report, etc.

Problem: How do we find that customer in the other locations' databases?

I don't see a way of making it work with the key field (customerID),
because that field will be different for the same customer across
databases. With the different locations operating simultaneously,
there's no way to guarantee uniqueness of the customerID across
locations. When a customer is pulled from another location's database,
it can't necessarily just keep the same ID, because it's likely already
used for another customer at that location.

My first thought was to use another field (or combination of fields)
that is likely (even if not 100% guaranteed) to be unique (such as
name+address+telephone#). The problem is that if any of those fields is
changed at one of the locations, it breaks the link. Even if they do
eventually get the address changed at both locations, there's no
guarantee that the employee will enter it exactly the same.

Does anyone know how to solve this problem?

Thanks,
Joe




Yahoo! Groups Sponsor
ADVERTISEMENT



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]