Database Re-Structuring!

[mad_mickey]

I have taken over the task of rebuilding some sloppily put together databases and need some help with the re-structure.

We have one process in our business for setting up a sales order and then printing off a confirmation, delivery and invoice notes. My predecessor has setup this process as 2 databases the first being that of a data entry database. The major problem with this database is that each line of an order is a new record in the database rather than each record being a new order which I think is the way it should be.

The second database is a forms database which is related to the data entry database by an order number. We then have a rather clunky and long winded process of pressing buttons in both databases to copy the order number accross to forms database which then views all the related lines of that order in a form using portals?

The reason I'm told this was done was to ease the printing of forms??! It has certainly not done this as we have one invoice that has 10 layouts allowing for the printing of different sized orders with portals covering 1 through to ten layouts!!!

I would greatly appreciate some ideas as to how to structure this seemingly simple process and also maybe a transition process for moving the one order multiple record scenario to one record with repeating fields.

Thanks for your help in advance.

[Søren Dyhr]

As long as you are sure you not are fooled by separation model, might your reasoning be right:

Take a look at this:

http://www.newcenturydata.com/downloads/separation_demo.zip

If this actually what's going on in the legacy base might it be a "If it ain't broke, don't fix it" ... but if clunky script-steps are making it happen does the odds look much more in favour for your reasoning.

I know that the lifeblood of all consulting is always to suggest something different that what's been working, and that other developers development style takes some getting used to.

But attempts to repair, is a dangerous move ... build a new one from scratch and test it against the existing, and be aware that you can't help always to develop with exaggeration of newly learned tricks, because the notion of reach of these, is an ongoing process involving your subconsciousness to achieve some sort of balance.

--sd

[David Jondreau]

My predecessor has setup this process as 2 databases the first being that of a data entry database.

It'd probably be best if you had two tables in one file, rather than two files.

The major problem with this database is that each line of an order is a new record in the database rather than each record being a new order which I think is the way it should be.

Sounds like your predecessor has it right here.

The second database is a forms database which is related to the data entry database by an order number.

You sure it's a forms database and not an Orders database? If not I think you need an Orders table. If the forms you print don't change very often, it's usually unnecessary to have a separate table for forms. That's what layouts are for.

We then have a rather clunky and long winded process of pressing buttons in both databases to copy the order number accross to forms database which then views all the related lines of that order in a form using portals?

Sounds like you have two main issues here.

One is a cleaner script to go from your data entry layouts to your printing layouts. It depends on your business logic, but you shouldn't need to click a bunch of clunky buttons.

The second issue is that it's generally a bad idea to print portals. Your printing layouts should be based on your Order Items table.

I would greatly appreciate some ideas as to how to structure this seemingly simple process and also maybe a transition process for moving the one order multiple record scenario to one record with repeating fields.

Don't do it. Repeating fields are not for general use. There for specific situations of which this is not one. Keep the order items in separate records.

Edited January 25, 2008 by Guest

[AudioFreak]

Sounds like you have two main issues here.

One is a cleaner script to go from your data entry layouts to your printing layouts. It depends on your business logic, but you shouldn't need to click a bunch of clunky buttons.

The second issue is that it's generally a bad idea to print portals. Your printing layouts should be based on your Order Items table.

Also just wanted to add to this that the layout you use to print your Packing Slip and Invoice should be a layout setup as list view containing all the Line Items. The header will be quite large and will contain Company Logo, Invoice Number, Bill To, Ship to, Etc.....

As DJ pointed out printing portals is usually not a good idea, and in this case it is not. You are limited by the number of items the portal can show. In list view if the records can't fit on one page it will simply print another page.

Michael

[mad_mickey]

Aaah, we're back online now. Spent half an hour writing an in-depth response this morning only to lose it all as the site was being backed up when I clicked send!!!

Anyway, thanks for your help so far and to cut that long email short I currently have 2 options.

1) I have already imported the external forms database into a second table on the entry/main database and explored that option.

2) I have another database process that was setup in the same way and I print the forms in the following way:

A script asks you which order you wish to collect information from (i.e. collects all the lines of the respective order) and presents that information on the required form.

I prefer the second as it only requires one table and therefore simplifies the process as I have other external databases that have to be added (as tables) shortly with contacts, product information, etc. My only issue is that any information pertaining to that order realistically needs to be duplicated accross all lines in that order. This creates another problem of copying and pasting data from numerous fields across multiple records/lines of an order.

Thanks for your help