Do I need a Totals Column, if related table holds the individual amounts

[uremroper]

Hi, I am currently moving from a MS Access background to SQL. Found both the speed, access to stored procedures and userdefined functions amazing.

I have an invoicing system that stores; invoiceID, invoiceDate, address etc, goods total and a discountTotal, in a related table I have invoiceID, discountReason and discountValue, you can have more than one discount per invoice.

Do I need the discountTotal in the invoice table or should I simply sum the discountValues in the discounts table with the correct invoiceID ??

My question is what is the best practice and performance issues on SQL

Many thanks
Mark

[Jim Doherty]

Hi, I am currently moving from a MS Access background to SQL. Found both the speed, access to stored procedures and userdefined functions amazing.

I have an invoicing system that stores; invoiceID, invoiceDate, address etc, goods total and a discountTotal, in a related table I have invoiceID, discountReason and discountValue, you can have more than one discount per invoice.

Do I need the discountTotal in the invoice table or should I simply sum the discountValues in the discounts table with the correct invoiceID ??

My question is what is the best practice and performance issues on SQL

Many thanks
Mark

Hi Mark,

The method of 'how' you retrieve/input/display data from this point onwards is fundamental to you really in understanding how to deal with your 'application' side processing efficiently. What method are you currently using to do this? My guess is you are using an mdb file still, and making changes to that to cope with any differences you may come across.

Personnally (and I know this cannot always be done) I generally rewrite entire mdb applications to be server side specific. What do I mean by this? well the principle has to be: 'if data is on the server... 'keep it there' as much as you possible can and only retrieve data that you actually need.

Stored procedures are the optimum for dealing with your data because they work to a compiled execution plan, a saved strategy if you like, that looks at a potentially complex SQL statement thereafter dealing with and retrieving data according to that saved plan. You will have already noticed the speed difference yourself.

The design of your database in Access is not always relevant in SQL Server. Yes... I know there is the upsizing wizard to get your tables to the server and so on, but if you are prepared to go down that route then for me, you really have to say to yourself "Is my database design still relevant?" given that SQL server will demonstrate to you that limitations you once had as to column joins in Access for instance simply do not apply anywhere near the same in SQL server or even maybe the 'Complex query' in Access is complete small fry compared to SQL servers capabilities to deal with those issues and so on.

Best practice and performance issues? well thats a mighty big subject dealt with on a day to day basis and for which whole books are devoted. My simple advice to you would be review the design of your database and take the chance to get rid of the "SELECT * FROM the biggest table in the universe" SQL statements.

Pay attention to field datasizes and types. Scrutinise your indexing strategy. Use stored procedures wherever it is possible or 'views' where not so possible and thus base your forms and reports on those objects as opposed to tables.

Pass 'parameters' to the server to retrieve only the data you need as opposed to 'everything and anything from the table or combination of tables'

I personally use and prefer Access ADP project files for accessing SQL Server data. Provided you have no need for localised tables on the client side I'd have a serious look at this option given that stored procedures and the views are exposed in the interface and your connection method is built in using UDL (Universal data link). These have hitherto been heavily promoted by MS in accessing SQL server data. You can use MDB files, of course you can, it is purely a matter of choice however I think if you look at the ADP format you will be pleasantly surprised. I know I was!

There is debate as to the 'best format' if you like for future use but 'whichever' choice you make will inevitably be subject to risk as any of the squillion or so VB6 programmers might well attest to when VB.NET came along.

In answer to your question on the discount total. Generally if you can calculate something 'on the fly' then you should do so rather than store static data paying homage to the priniciples of normalisation. However, it is not always relevant in my view because you might for instance need that data in a fixed format ie: 'it never gets changed' and which can suitably be stored as a 'discount total' that was relevant AT THE TIME of the transaction and which was based on a specific formulae at the time that also remains unchanged and which in itself could be statically stored in the row. Conversely purists might persuasively argue differently and say for instance all you are doing here is increasing backup times and creating an environment for redundancy. Its a matter for you!

Provided you know the boundaries of your design then the merits of storing data statically can be advantagous, in that subsequent SELECTS of that data would not need to be subject to potential intensive processing to rekindle tose values... hence releasing the processor to do something else.

Hope these observations FWIW helps you

Regards

Jim :)