Multiple Databases...one interface?

[B. Keith]

What I have is a standard set of databases...a many-to-one relationship between product.fm5 and plantdesc.fm5. People can open up the rose database and see how many different pot sizes and plants we have for each variety.

This works great for the various departments (perennials, roses, bedding plants etc.) as they each have their own database with custom value lists etc.

Last year I only uses one non-related database and was able to do administrative functions (clean-up, global changes etc.) and post it on our web site as a price list via cdml. This year I have 7 different databases and am at a loss to figure out a structure which will allow me to access all the records in the various files... I was trying for some sort of master related databse but so far it doesn't seem possible.

Any ideas on the basic structure this should take?

Nuts and Bolts

product.fm5

each plant has an plant number

10000, 10001

each product has a suffix

10000-01, 10000-02, 10001-01, 10001-02

Plantdesc.fm5

plant descriptions are related to products via the plant number

What I want to be able to do is

A) post a single, searchable database on the website, while maintaining a smaller more managable database for each of the Dept. Heads that they can revise add and delete to without being bothered by other department's inventory

B)Be able to do global searches, updates and reports based on all the records as well as (if possible) be able to do imports and exports to interface with the accountant's PO system.

help?

Bruce

[tlsparker]

This sounds like it could be as much a political challenge as a technical one. I recently worked with a large nursery where each department head had their own database and I know folks get pretty attached to their own databases. However, if there is any way to bring all the databases onto a central server, that would probably make your life easier.

If it was me, and I wanted a web interface (and I had the powers of a ruthless dictator), I would use Lasso and give everyone access to the databases through a browser environment. However, you probably won't get away with that.

Structure-wise, based on your description, it seems you can get away with two related files, a plant database (one record for each plant) and a product database (one record for each product). Link these by the plant code. Descriptions can be a field in the plant database. Host these files on a server in multi-user mode. Perhaps each department head can maintain their own layouts if they desire to have a unique interface. Someone, probably you, will need to coordinate structural changes.

Once you have a centrally served database, connecting via a web browser (whether you use Lasso, CDML, etc. etc.) will be more stable than if you try to connect to several databases scattered across a network.

Sounds like a fun challenge; good luck to you.

Tom Parker

[email protected]

http://www.tgparker.com/filemaker

[B. Keith]

Thanks for the advice... the politics are most evident when I explain that the bedding plant manger has to deal with all 5000 perennials in his searches etc.

I guess I will try to design some scripts for the searches and not let them use the find command...

Any other suggestions would received gratefully.

Bruce

P.S. What were you doing for the large nursery? Anything I can use/steal/buy? I'm pretty much trying to reinvent the wheel here because plant people (and their plants!) are unlike anything else I know...

[tlsparker]

The system that particular nursery used included inventory, quote, order, invoicing, shipping, seed sources, and other basic accounting functions. That particular system was developed in MS Access and is a custom, proprietary application written by an in-house PhD plant guy who was a programmer in a former life.

The development approach was to pull together several satellite databases into a central system while allowing each department to request custom reporting and querying features. The system worked great, probably because it was built from the ground up by someone who understands how horticulture/botany people think.

It all boiled down to modeling the business flow.

Give a yell if you want some insight from a plant guy.

Tom Parker

[email protected]

http://www.tgparker.com/filemaker