Cleaning up an existing related database

[tapededitions]

I have been given the project to take the existing system and make it more efficient. I need to do this without loosing the existing data. Right now there are over 23,000 records.

The system is a related group of 3 databases, Customers, Inventory, and Orders. Some of fields are in all 3 databases, some are in 2 of them and some in just one. Right now each database has the fields set-up separately and they use importing and exporting to change the data. Is there a way to link the connected fields so that changes could be made without importing constantly and without loosing the data.

For example:

In inverntory there is information on books. Inventory status, date sent, date returned, sent to whom, etc...

In Customers the imformation is the Customer preferences and account information etc...

In Orders there is information on the Customer what book titles they have had when they got it, when they returned it, etc...

I want to be able to condense the data so there will be less entry mistakes, but I need to keep a list of all the book titles that customer has ever has so that they will not get repeats. At the same time I need to know where each book is (which customer has it or if it is in stock).

Where should I go from here?

Any help would be wonderful!!!!

[Kurt Knippel]

The simplest system that will capture the information with the most minimal repetition is the following:

Customer.fp5

CustomerID_key

Name

Order.fp5

CustomerID_fkey

BookID_fkey

Date Ordered

Date Recieved

Inventory.fp5

BookID_key

Title

ISBN

Anything else that you add is simply gravy. By this method the only data that is repeated are the ID fields in the Order.fp5 file and this is so that you can associate many customers with many books.

Now since I do not know where you are in relation to this basic system, I cannot really say how you can improve, but maybe this will be enough to get you started.

[Rigsby]

If I understand your problem correctly, someone has done some pretty sloppy programming! Getting information to move between FM databases is not only relatively easy; it’s what makes FM so useful. Importing and exporting from one file to another is not the way.

If I understand this, a scenario (just for one field) would be something like this:

You have a field (say <NAME> ), which is in all three files. To move the contents of this field from file1 to file2 and to file3, you export/import the data! This is terrible. What you should be doing is this:

Create a relationship field in File2, relating to File1 – (say <CUSTOMER_ID> ) then in File2 you can show any information you need from File1 in a portal

If you need information in File2 that is “imported” but then never changes again (invoices or order dates etc. for example) – use a lookup based on the relationship

If you change fields in your current files to related lookups, the existing data will not be lost, as FM will only put new data in the field, if the field is empty or you force a new lookup to replace it. But you need to be careful here. Make backups of the files along the way (i.e. after every change) keep them all.

However,

Sometimes, it’s better to start something again – maybe that’s your problem here! However, starting again is not always as difficult or time consuming as you might think. You have all the data you need; it’s just split over different databases. Let me give you a quick structure solution here:

You need a customer database, with all the personal information on each customer. You then need an Order database as a related file, where every order is stored. That’s it – so I don’t really see your problem. Forget inventories at the moment. Concentrate on getting the right information in the right file first. One trick here, that many people fail to realize, is that if you export records from a FM database, say into a Tab-Text file, you can export not only the fields from that database, but also from your related databases, then import everything back into a single file. This is an excellent way of cleaning up sloppy databases, where information has been put in the wrong place. When you’ve separated all the information (grouped together what should stay together) define relationships between the databases – deleting repeated fields – you only need to store information once.

I hope this helps at least a little! Cleaning up someone else’s mess is always harder than starting from scratch.

Rigsby