Single Table instead of Multiple

[sal88]

Hi all

 

Something I've been pondering for a while...

 

Our invoices contain multiple data types: Items, Labour, Recurring services.

 

In order to have the invoice list these data types on a single invoice I have had to have them all reside in one table, instead of the ideal which is one table for each type. The result is a lot of confusing relationships and lots of fields and hiding on the invoice layout (something that might be simpler thanks to FM13's hiding option).

 

We have separate layouts for each data type - for the purposes of browsing. This means that there is a bit of scripting to be done i.e. to ensure that when a search is performed in the Items layout, it specifies that none of the other types are to be included in the query. This method doesn't quite feel solid enough - but generally does work.

 

Recently I've been thinking that an alternative would be to go back to having the different types in their own tables. Invoice generation would therefore pull those records in to a single table, on a temporary basis.

 

Another feature we have is being able to preview invoices before they are generated in a list format. This shows all the invoices that are about to be generated, giving the user a chance to edit. This wouldn't work so well with the above solution because after editing, it would have to relay those modifications to the multiple tables, whose records were pulled in to the temporary table.

 

Or should I be looking to have a single table which the invoices are based on, with which records from all the other tables have a relationship? 

 

Any advice appreciated.

Many thanks

[Wim Decorte]

 

 

Our invoices contain multiple data types: Items, Labour, Recurring services.

 

In order to have the invoice list these data types on a single invoice I have had to have them all reside in one table, instead of the ideal which is one table for each type. 

 

Why do you think the ideal is to have one table for each type?  Can you expand on that a bit so we can follow your reasoning?

 

My take on it:

Invoices go in one table, invoice line items goes in another.  Labour / Items / Services is just an attribute of the invoice line item.  It's a descriptor not an entity.

 

Elsewhere in the system there may be different tables for these types of "product" but even that I have a hard time agreeing to.

[sal88]

I guess the idea comes from the fact that aside from the invoicing stage, the different types of data do a few different things and are inputted in a way that is intuitive to the end user - they feel more like separate entities rather than types.

 

E.g.

 

 

when an engineer inputs their work the layout has:

1 Labour field + 1 'hours billed' field - different payment units can be selected (e.g. hourly rate, daily rate)

Items portal - they can bill for multiple items

mileage portal - they can input multiple distances covered (here is another data type not mentioned before)

Recurring service - e.g. 'ad hoc' or 'support' - this is the monthly contract that the job is associated with, depending on the service it is associated with, the time spent may not actually be billed to the client as it might be part of an all inclusive service (though it will still be displayed on the invoice - just under the 'support' category of labour'

 

 

Recurring services also come in the form of 'non labour' ones, i.e. work can't be associated with them. The recurring service type has start dates, frequencies (monthly, quarterly etc), expiry dates. The labour form of this data type effectively dictates when invoices are generated, as all billed work is associated with them.

 

Therefore, when the invoice is generated, it is not simply a case of line cost x quantity = total, but there are a few more calculations and billing fields specific to the data type. The result is a cluttered layout.

 

When analysing the data in list views, the user wants to see them in separate layouts - as mentioned, this requires extra script steps in the search process. With summaries being included, this can slow down the loading of the records, unless they've already constrained upon the start up script. These summaries are based on different fields depending on the data type.

 

 

Further development is a little slow as when finding fields I have to trawl through loads. The relationship graph isn't that great to look at because it's not always obvious why a relationship is there (there are loads of occurences of the same table). I would have thought it might be better performance wise if there are multiple tables because relationships can skip the 'unrelated fields'.

 

 

 

Has this at all furthered my case? 

[comment]

How do you currently print your invoices? I presume each type of items needs its own set of columns?

 

 

The relationship graph isn't that great to look at because it's not always obvious why a relationship is there (there are loads of occurences of the same table).

 

I believe you will end up with (at least) the same amount of relationships if you switch to separate tables. Also, if you name your occurrences in a meaningful way and/or add notes to the graph, perhaps their roles will become more obvious - so this one is not "furthering your case", I'm afraid.

[sal88]

That's right. So within that same table I have fields called 'Invoice_header_1', 'invoice_header_2' etc. These will change to 'description', 'personnel', 'Cost', 'quantity', 'Hours', 'Days' etc depending on the data type. The recurring service data type will only display 3 fields, items 5, labour 4.

 

The table currently has 131 fields.

 

Records are grouped by type (RS, item, labour), after that labour is grouped by the recurring service that it is associated with, and after that labour is grouped by the payment unit type (day, hour, half day etc)

After each of these groupings there are subtotals (again more fields in the table)

 

 

I think that my main issues are the script steps involved in browsing the different data types, and potential performance issues of having the same table being constantly referred to for varying calculations and summaries (no idea if that's how it really works though). 

 

When it is time for a recurring service to be billed, it is duplicated in the same table, and the invoice number is attached to the new record. The uninvoiced record looks to these previous records to know when the next date for billing is. This is one of the examples of the table having to refer to itself.

[comment]

Well, the thing is that if you switch to having only the three separate line items tables, you won't be able to print your invoice. So you're looking at either a single table, or four tables: three for the line item sub-types and a super-type table holding the fields that need to printed and/or summarized. It's a tough choice because neither is particularly easy to set up and maintain.

 

In your place, I would take a long hard look at those 131 fields and see if they can be streamlined, before rushing to embrace the new devil.

 

 

As for recurring services, this seems to be unrelated to the main issue here, but IMHO they need to have a parent table, and the uninvoiced record should take its data from there, instead of looking for previous siblings.

[sal88]

Thanks for all the advice, I'll look in to my solution further and see what the best route is.

[sal88]

What I would like to know though, is if there can be negative effects of having a table refer to its own records rather than those of a separate table?

[comment]

I am afraid I don't understand your question. Can you rephrase?

[sal88]

Let's say I have 5 tables, all with calculations dependent upon the relationships between them and therefore referring to other tables. I instead have just the 1 table and replicate those relationships and calculations by having self join relationships. 

 

Would the fact that calculations are always referring to the same table in which they reside, rather than other tables, cause performance issues?

[comment]

I believe the performance would be unchanged. However, I am confused regarding how this fits with the discussion so far. This started with expanding one table into several, now you are talking about combining several tables into one?

[sal88]

I'm just examining reasons to depart from the current single table configuration. You've just given me one reason not to   

Thanks