473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
Dec 11 '07 #1
1 940
Jim Doherty
897 Expert 512MB
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 :)
Dec 11 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: The Bit Bandit | last post by:
Hopefully someone can help me create a query that I'm having some trouble with. I have three tables: invoices, invoicedetails, invoicepayments The fields are: invoices -------- InvoiceNo
1
by: Dalan | last post by:
I have tried both methods of using DSum and creating a Function to address summing some number columns, but to no avail. Since this has been a popular topic over the years, I'm sure I'll receive...
3
by: CSDunn | last post by:
Hello, I have 14 fields on a report that hold integer values. The field names use the following naming convention: T1Number, T2Number ....T14Number. I need to get a 'sub total' of all fields as...
6
by: KashMarsh | last post by:
Trying to show running totals on a report, except it needs to show one total amount and values being subtracted from it. For example, the report shows a Total Inventory amount (TotInvAmt). And...
2
by: coleenholley | last post by:
Here is the format of the datagrid/table I need to create. Since the static data is not the same for each row, I'm at a loss as to how to populate that column...? Static Data Dynamic Read...
1
by: Rippo | last post by:
Hi I have the following FO XSL document with the following data I am trying to add sub totals for each group in a XSL document but cannnot seem to work it out! Output would be something like:-...
9
by: JJM0926 | last post by:
I'm trying to create a running totals query in access 97. I have followed the directions on how to do it from Microsofts website article id 138911. I took their code they had and replaced it with...
1
by: muld | last post by:
I've inherited a database system designed to calculate sickness absence statistics. There is a main table with an employee ID which also holds the number of days worked by that person in a year ....
4
by: jbrumbau | last post by:
Hello, I have been successfully using a database I've created for several months to populate an equipment list for a project we've been working on. However, the form has recently stopped working...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.