July 19, 2012 at 1:58 pm
Hello! I've been tasked to start thinking of a method to collect and present sales data for monthly sales for the business owner. I've got a good idea of how to gather the data and present it, but one part is bugging me; namely, how should I organize the data?
My thoughts at present are that I could do one of two things: First, I could have a table with a row for each item, and columns for each month. Calculations could be done elsewhere to determine monthly sales, and the column for each item could be updated to reflect the sales for that month. Alternatively, I could create a table for each month, and track sales for each item in a single column in each table.
The problem with both methods is how past data would be contained; if I go the single-table route, I could create archive tables to contain previous years at the end of each year, and copy the current table to the archive table, then clear the current table, all on a yearly basis. For the table-per-month approach, I could additionally label each table with its year; however, this would lead to quite a bit of database clutter. The archived data would be important in our calculations, since we want to see the overall performance of any given item.
At the moment, the single-table route seems like the better choice in my eyes, but there's inevitably something or another that I haven't thought through enough on my own just yet :-). Would someone be willing to spare a few minutes to point out any faults in my current idea, and perhaps an approach that hasn't dinged in my head yet? Thank you for your time!
- 😀
July 19, 2012 at 2:26 pm
Something like this. Don't summarize or group your sales in tables, store all the data in 1 table as granular as you need it to be and let your reports do the grouping and aggregation.
CREATE TABLE Sales(empId int, salesdate datetime, customernumber VARCHAR(250), productId int, quantity int, amount money)
This is oversimplified, but gives the main idea.
Jared
CE - Microsoft
July 19, 2012 at 2:49 pm
SQLKnowItAll (7/19/2012)
... Don't summarize or group your sales in tables...
You're probably right and probably not.
As I understand, he's trying to summarize data from an already existing sales table. If that's the case, he could use a table to summarize depending on the volume of information or capabilities of the server.
The design would depend on how many items do you have, if the sales follow Pareto principle (or 80-20 rule) and, most important, how does the business owner wants to see the information.
July 19, 2012 at 3:34 pm
hisakimatama (7/19/2012)
First, I could have a table with a row for each item, and columns for each month. ...Alternatively, I could create a table for each month, and track sales for each item in a single column in each table.
The problem with both methods is how past data would be contained ...
Definitely only one table. But one row for each unique item and month, rather than one column per month.
Cluster the table by ( date ) or ( date, item ), depending on your specific needs.
Something like:
CREATE TABLE dbo.sales_summary (
date smalldatetime NOT NULL, --[or datatype = date in SQL 2008]
item varchar(20) NOT NULL,
sales decimal(9, 2) NULL,
...other_columns_as_needed...,
)
CREATE CLUSTERED INDEX sales_summary__CL ON dbo.sales_summary ( date ) --( date, item )
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 19, 2012 at 4:06 pm
To take a little different approach to the discussion, you should NEVER design your tables based on what you want in a report. Design your tables in such a way as to maintain data integrity and normalization. If you design your ddl correctly the dml to retrieve becomes very easy. If however you design your data for a specific output you will have a disaster trying to pull the data back out.
Your project as you described is to collect and present sales data. Think of this in two steps. First is to collect/store the data. The second step is to retrieve the stored data and manipulate it into a presentation.
With that in mind what are the elements of a sale?
Customer, date, skus, subtotal (calculated), tax, shipping, hazmat charges (if applicable), etc.
With your data in that type of structure it is super simple to create your report.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 19, 2012 at 10:05 pm
Sorry for the late reply! Was quite tied up at work, and then more so at home :-). Luis is correct, we already have a table charting all of our sales and the dates they were sold at; I should've been more clear on that.
Basically, the sales data is stored in a table that just records when the sale was made; what the business owner wants is a report that will show him what items sold in each month of the year, and how many of those items sold. The item name and the quantity sold per month are the only important factors.
I figured the single-table approach was the correct one, moreso because of the data clutter than anything. Honestly, I think I could probably handle the whole report aspect entirely through SSRS, but we don't have it at my workplace :(. Failing that, I suppose using the single-table approach, keying it to our inventory table on product ID, and feeding the data from that table to a report in our front-end would probably be the most practical choice.
Thanks a lot for your advice! Helped me soundly nail down a solution 😀
- 😀
July 19, 2012 at 10:09 pm
Ok, let me ask you this... How much data do you have on a monthly basis? How many months worth of data do you have? I ask because if it is millions of rows of data a month, then best to warehouse it and maybe do some aggregating. However, if it is only thousands per month there is no reason to denormalize your data into a summary table. Just write the query for the reports and put it into a stored proc. Then create your reports. Otherwise, you have this other table of redundant data.
Jared
CE - Microsoft
July 20, 2012 at 11:25 am
Jared, we've got sales data going back to 2007, and we add about a thousand rows to the table daily at present, though that rate could increase as time passes. The sales table is quite wide, however, with 141 columns (and most of them are redundant or unused; the vendor's decision to set up the table like this is quite perplexing!); I didn't know if that would factor into the need for a separate table or not. We're sitting at just short of 200,000 rows in the table at present, with the thousand-rows-a-day metric having become the norm around the beginning of this month.
Also, our frontend is Microsoft Access, and it's been a little quirky about working with calculations from a table to a report; however, I'm sure I could poke around with some stored procedures that would hack away at the data enough to get just what's needed, and essentially take the workload entirely out of the hands of Access. Another random problem that could come up is that management could decide that item ID and sales amounts per month are all we need right now, but once development starts, other requirements might be added in and the general purpose of the project could be reworked... But thinking about it, reworking a procedure would be so much easier than trying to fumble with an already-made table to get things working the right way. Alright, that last point pretty much settles it :-P.
Thanks for the guidance, though! Wouldn't have considered the alternative nearly as much without a good analysis of why my approach might not have been a good idea :-).
- 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply