This should be easy, but it's getting complex...

[Amund]

I'll try to describe my problem as easy as I can (Don't laugh!).

I am developing a order/storage database for labelproducts and need help in solving how to organize data and databases. For the record : I use FMPro 5,0 and the database system will be accessed primarly thru a browser (cdml). It is not a e-commerce/shop solution, it is more like a in-house storagesystem.

The system looks like this (at the moment):

Ordermain.fp5 (F: Ordernumber, CoustomerID (CID), OrderDate, Est.Delivery, Delivered)

OrderDB.fp5 (F: Ordernumber, CID, LabelID, QuantityOfLabels)

Storage.fp5 (F: StorageID, CID, LabelID, Quantity)

Additional databases:

LabelDB.fp5 (F: LabelID, Description/Name, Picture, Quality, +++)

CoustomerDB.fp5 (CID, Name, Address, Att, +++)

(Furthermore the system includes a categorydb for labels, login database, security and so on)

----

When a coustomer calls me and need labels, I establish an order in OrderMain.fp5 (Creates a unique Ordernumber --> OrderNO),

I select the labelvariant and quantity related to OrderNO in the OrderDB.fp5.

Now I order the labels from the printing plant, this takes about three weeks to produce, so I like to place the order "on hold" until the labels arrive.

When the labels arrive I'd like to update the StorageDB.fp5 with the delivered quantity. Now my problems appear!

StorageDB.fp5 might allready include some quantity of the label variant we placed an order with. As a part of the service I want my coustomers to have the possibility to see what labels are in order now, how many labels is in our storage, what quantity did we order of a certain label the last time we placed an order?

So StorageDB.fp5 (or related databases) must display :

Current storage quantity (LabelID, quantity)

Last incoming labels (LabelID, quantity)

Quantity on storage before last incoming labels.

----

To make this case a little more complex:

If my coustomer orders 600000 labels, we offer him free storage and split the delivery and invoice in 3,

400000 labels 2 delivery/invoice,

200000 or less he has to come for the labels at once and pay immediately.

So when a coustomer orders say 600000 labels he has 3 "outtakes" (verybadenglish!), after some months he checks the system and realizes he need more labels as he has used 400000 labels and have only 200000 labels left (and 1 outtake - Yes, this is getting even more complex than infant school math!)

So he decides to order 400.000 new labels - of the same kind (2 outtakes for the new order).

Now, to the point, I want to display:

Total amount of a chosen label (Earlier orders not delivered + new orders arrived storage)

The total amount splitted up in the separate orders showing how many "outtakes" the coustomer has left.

So for this coustomer the system should display (After new order arrive storage) :

Main storage display (-page):

Labelid: 150-01 TotalQuantity: 600000 Based on 2 orders

Detail storage display (-page):

Labelid: 150-01 Quantity(left): 200.000 Outtake 1.:200.000 2.:200.000 3.:Open

Labelid: 150-01 Quantity(left): 400.000 Outtake 1.:Open 2.:Open 3.: ---

I think this should explain what my goal is.

So what solution do you suggest? Should I use scripts to move data from OrderDB.fp5 to StorageDB.fp5 or create a new related database?

Should I join all data on a label in the StorageDB.fp5 on one record (2 or more orders on one "label-record") or -

- keep all records with data on the same label separate, so to display a total amount of a given label depends on a sum of several records in StorageDB.fp5?

Just to extend this descripsion, a usual order contains 600.000 labels dispersed on up to 15 label-variants.

The system takes care of my most important coustomers, at the time 15 unique users and 150 unique labels.

If you actually read this far, that's impressive, thanks!

You are welcome to comment or suggest any part of my questions...

And finally, yes I know, this is a far to easy problem for such a long text... It's just that it is so difficult to explain both on a language I hardly manage and about a topic I evidently have a big time problem managing!

Thanks in advance!

Best regards,

Amund Rasmussen