one table or many tables?

[nox]

Hi all,

I am working on quite a complex invoicing solution. There are 3 departments that issue invoices - each need a little bit different invoice layout and some different fields and different related tables.

For example one department deals only with products so it will pull products from the separate products table.

Second department deals only with services. So it will not use any products, but will write their own description of service into invoice.

There is third department similar to second but also needs a little bit different layout/fields.

They all should follow same Invoice Serial number though.

The question I have is:

1. should I try and keep them all in one "Invoices" table? This way I need less scripts and there shouldn't be a problem with serial number. However there can be problems when browsing the records because some invoices have different layout than others. So for example on "Services deparment" invoice record the invoice from "products department" will not display correctly.

2. should I keep all departments in separate tables?

I can see a problem in generating correct invoice serial number through the 3 tables this way. And I would need to duplicate/change lots of scripts so that they would work in those tables.

I will be much thankful for any ideas/your experience in this topic.

Thanks,

j.

[David McQueen]

1. Off the top I would have one table.

2. I do not see mention of it in your post, but a classic invoicing system has two tables, one for line items and the other for invoices. May not be applicable here.

3. You can handle the different displays and browsing by directing to different layouts when an invoice is selected, or alternately, by having the depatments isolated when a person goes to browse invoices so that all they see is their own.

Just a few thoughts.

[nox]

Thanks David,

I was leaning more towards the one table solution also.

3. You can handle the different displays and browsing by directing to different layouts when an invoice is selected, or alternately, by having the depatments isolated when a person goes to browse invoices so that all they see is their own.

What is the best way to do this? Performing find for relevant records on layout enter or some other way?

[RodSierra]

Well not to be contrary, but I would stick with a two table solution, one being a document header table, containing information about the type of document, critical dates, customer id, discounts, surcharges, taxes, etc. Second is you line items containing reference to the Items, quantities, notes comments, in this table you can add as many additional fields as necessary to accommodate special documents, such as packing lists that may contain additional information such as weight cubes, etc, simply by adding fields. Your final documents are then generated from the line items table, pulling header info from the header table. Related by one document serial number field and containing only the fields in line items pertinent to that document .

[David McQueen]

Well not to be contrary, but I would stick with a two table solution, one being a document header table, containing information about the type of document, critical dates, customer id, discounts, surcharges, taxes, etc. Second is you line items containing reference to the Items, quantities, notes comments, in this table you can add as many additional fields as necessary to accommodate special documents, such as packing lists that may contain additional information such as weight cubes, etc, simply by adding fields. Your final documents are then generated from the line items table, pulling header info from the header table. Related by one document serial number field and containing only the fields in line items pertinent to that document .

Actually not contrary at all. It is the classic way to do it because it makes sense.

The original question was whether or not to have a separate table for each department. That's not a good idea. The items can only be brought together with great difficulty.

In a one table solution, even assuming that there are only ever a limited number of individual items, you end up with problems searching for data in individual line items because there is more than one line to search.

The upside, if you are doing it yourself and it is intrinsicly simple is you understand it because it closely mirrors what you are doing with paper. So it runs, it is relatively quick and the learning curve is not too great. But no, not the greatest design solution.

[David McQueen]

...

What is the best way to do this? Performing find for relevant records on layout enter or some other way?

You can script a find. Set up a find screen with global fields if the criteria keep changing. The other way is using go to related records commands which would use some form of self relation to isolate, most likely by department, grouops of records.

They all work depending on how much user interaction and flexibility you want at that point in the program and how comfortable you are with the techniques.

I usually use GTRR for automated finds and a user interaction screen either populated with global fields or directly into find mode but well marked as such so people do not think they are entering data for more flexible and interactive demands.

[nox]

Thanks for all your answers. I will go with one table solution and see how it goes.

cheers!