Understanding Alphasports invoicing

[lkatz1]

Is there anyway that we can understand how the invoicing set is linked together ? which is linked first etc? why do we need so much tables? And which is the actual table that prints the personal customers invoice?

Thank You

[Stan Mathews]

Re: Understanding Alphasports invoicing

which is linked first etc?

Select the invoice set in the controlpanel. Right click on it and choose Edit set. You will see the linkages. (hasn't changed since at least V5)

And which is the actual table that prints the personal customers invoice?

Select the invoice report in the controlpanel. Right click and choose Design. Scroll through the Drag_Drop list in the upper right corner and you will see check marks alongside the objects that appear on the report separated by table.

[johnkoh]

Re: Understanding Alphasports invoicing

why do we need so much tables?

normalization: Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy

[Ted Giles]

Re: Understanding Alphasports invoicing

NORMALISATION. As John says, this is to avoid duplicating data or having a lot of empty fields in your database.
There are many layers to normalisation called Normal Form. Layer 3 is as far as most people go, but 4 and 5 are sometimes attempted.
On the other hand, RATIONALISATION makes the database easier to maintain and allows some redundancy.
As one of the members says, "normalise till it hurts, rationalise till it works"

[Tom Cone Jr]

Re: Understanding Alphasports invoicing

David,

The Invoice report could be based on a two table set, without joining the customer, product or vendor tables. All you'd need would be the invoice_header and Invoice_items tables.

However, when field values from the customer, product or vendor tables are needed on a page you would have to define a calculated value which would open the desired table, set an index, find the desired record, close the desired table, and return the desired value. This could be done with Lookup() functions, for example. A series of these lookup() expressions could be incorporated into your report.

Opening and closing tables are two of the slowest things alpha does. A minimalist approach to set design would require these slow functions to be repeated over and over, once for each invoice, and possibly once for each detail row in the report. In fact they'd be called each time a new field value must be retrieved from these three supporting tables. The likely result: a very slow report.

Including the customer, product, and vendor tables in the set design permits Alpha to open these 3 tables only once, no matter how many invoices are being printed and no matter how many detail lines each invoice might contain. Why? because the set keeps the desired records in these supporting tables linked automatically. No lookup() calc fields needed to retrieve field values.

I suppose if one wanted to really do the report the "hard" way, you could base it on the invoice_header table alone, retrieving many field values from the invoice_items table by a further series of lookup() calc values. Not only is this much more difficult to design, its performance is likely to be pitiably slow.

-- tom

[Tom Cone Jr]

Re: Understanding Alphasports invoicing

John,

Minimizing redundancy is a key value of normalization. Efficient use of available disk storage is another.

[Stan Mathews]

Re: Understanding Alphasports invoicing

David,

Remember that the AlphaSports data structure is essentially unchanged since V5. Additions have been made and the forms and the reports have changed to reflect new features added to Alpha. No one ever claimed that the design was the best in the first place. AlphaSports was designed to demonstrate what Alpha can do.

If you have a better way, or think you do, feel free to try it.

[Ted Giles]

Re: Understanding Alphasports invoicing

One other thing. The overhead of a Set is minimal. The relationships are built as they are needed. You don't have loads of additional data taking up space.
There are many instances when you might want a realtionship turned on its head, so one set might be;

Customer
-- > Invoice

And the same tables used to create a Set;

Invoice
--> Customer

[Charles Hoens]

Re: Understanding Alphasports invoicing

At the risk of being "pooped" on, I'm going to disagree a wee bit with normalization as it affects us today.

Like most of you, I came up from the dark ages with computers where the saving of a bit here or there meant seeing if the program would run at all. Therefore, we all have this in the back of our minds and I, like all of you, have worked very hard to minimize dbf's and apps. But recently with the Terrabyte drives and gigabyte ram's and so forth I've become a slob. When I first started in dBase II with Ashton-Tate I had to be very careful with the size of fields and how many you had, etc. Now, I routinely construct dbf's with no regard whatsoever on the field sizes or even how many fields I use. Who cares? Even with a million records in one of my apps there seems to be no need anymore for saving bytes or space.

Point is, I'm now becoming a programming slob, and I regularly duplicate dbf's and fields from one area to another. If I even think that I will need some field or other somewhere else (maybe for a report or something) I copy it over. It ain't good, but it's reality.

[MoGrace]

Re: Understanding Alphasports invoicing

There is redundancy to be considered for sure, but there is also the need for intuitive ease of use. Hence even Alpha Sports copies the product info into the invoice line item table because that table can then be further edited without affecting the default product item. Wherever I have a repeated need for a lookup list I find a table suits better than typing out the list in a FRUL, eg. That provides uniformity for the end user as well as the ability to adapt when changes are needed.