Hi,

FMAdvanced8.5, FMServer 8.0, Windows XP/Windows 2003 Server, TCP/IP
protocol

I would like to know your ideas on the best way to archive records and
keep the links to other related tables.

Take the following tables:

CUSTOMER_Current
CUSTOMER_Obsolete
ORDERS
INVOICES

Customer may be in one situation or the other, but not both of course.
Customers might move from Current to Obsolete and from Obsolete to
Current (it is not a 100% fixed situation, more a 95% in real terms)
Both may have orders and invoices, although only the CUSTOMER_Current
table may generate new orders and invoices. An obsolete customer
might have invoices generated when the customer was active.

I need to move customers from the Current table to the Obsolete table,
but keep the relationships they had with orders and invoices (users
need to be able to access those orders and invoices from the client
table - a portal or whatever).

What I have in mind:

- Create a join table between CUSTOMER_Current and ORDERS
- Use the same join table between CUSTOMER_Obsolete and ORDERS
- Create a join table between CUSTOMER_Current and INVOICES
- Use the same join table between CUSTOMER_Obsolete and INVOICES

When I move a record from Current to Obsolete, I need to:

- Move the record from Current and copy all the values to Obsolete
- Somehow copy the key that links Currents with Orders and Invoices,
to the second join table.

I could alternatively create a join table between Current and Obsolete
just for the purpose of avoiding the need to copy fields from one
table to the other. This way, when I move a customer from one
situation to the other, I just copy the key and maybe fill in some
compulsory fields that are present in one table but not in the other
(for instance, Obsolete_date), and I would still be able to access the
rest of the info for that customer.

What do you think? is this correct? what is wrong? is there a better
way to do it?

Thank you

Carlos Pereira <carlosp- (AT) nnhotmail (DOT) com> wrote:

I prefer using one address (customer) table only. I use a field to
determine whether the address is valid or not. You can do the same with
current and obsolete, and may script searches to make sure users will
find only the current customers.

--
http://clk.ch

Thanks Chirstoph, but my question was about the model I propose. I
have already tried the kind of solution you propose, and it is fine up
to a certain point where you have to script very complex searches,
finds and replaces, exclude records from reports, etc. All this is
much easier if you take advantage of FM7+ (8.5 actually) capability to
create unlimited number of tables. I want to use it, but I would like
to know if somebody else is using it and how.

Any comments?

On Sat, 13 Jan 2007 13:35:28 +0100, clk (AT) freesurf (DOT) ch (Christoph
Kaufmann) wrote:

Carlos Pereira wrote:
I see that you don't like Christoph's answer, but it is the best
answer. Feel free to make an archive table for records, but in my
opinion, it should be a one way street for old records, not a
semi-temporary storage location for out-of-touch customers that might
come back.

A single field can pretty easily keep track of which customers are
current for finds and reports, etc. I have a "Customer Type" kind of
field which can contain "Customer" or "Ex-Customer" among other types.
The trouble caused by having old customers in your file is eclipsed by
the trouble and complexity of trying to move customer records between
two tables and still keep everything straight. That's my comment.

Thank you Paul. It seems that you both agree on a single table an a
flag field as the best option. It is what I have now, anyway.

Thanks

On 17 Jan 2007 11:30:45 -0800, "Paul Bruneau" <paul (AT) ethicalpaul (DOT) com>
wrote: