January 7, 2009 at 5:20 pm
Is it good design to create separate table for daily inventory?
This way maybe tablediff.exe can be used to find the difference for different day's inventory?
Any better design to keep the daily inventory?
I need to save the daily inventory because a delta report needs to be generated for different days.
How to save the data and how to do the delta report?
January 8, 2009 at 2:11 am
Have one table and add a "date" column to it.
Deltas are easily managed anyway with this
select *
from (select * from table1 where date = ... ) as t1
full join (select * from table1 where date = ... ) as t2 on t2.product = t1.product
N 56°04'39.16"
E 12°55'05.25"
January 8, 2009 at 6:01 am
I agree with Peso.
Think about it. First, you'd need 365 seperate tables for just one year worth of data. Second, your queries would have to be changed, every single day, as a new table was created (although you could get around this by having a view that combines the tables and then you only have to change the view every day, but that's still a lot of daily work).
How much data is going to put into this table on a daily basis? If it's millions of rows, creating a partitioning schema might make sense, but anything else probably doesn't justify it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 8, 2009 at 7:19 am
Thanks a lot for the comments. I agree with one-table with date as a column. It is really hard to maintain that many tables.
The inventory for one customer is at most 10,000 records.
Another design issue is that there are about 40 customers, is it better to create a separate table for each customer or put all the customers in one table and add customer name as another column?
My boss and I prefer with separate table for each customer, this way it is easy to maintain, but I am not sure what is the best way in practice.
January 8, 2009 at 7:24 am
I would have all customers and all inventory in one table and have date as one key column and customer id as one key column.
N 56°04'39.16"
E 12°55'05.25"
January 8, 2009 at 7:32 am
I would put the customer information in a common table. 40 customers = 40 tables, with 40 different queries and 40 different indexes and 40 different maintenance plans... Oh, and now we have 41 customers so we need to add another set of queries and another set of...
I mean, there are reasons why you would partition data, but generally, the whole idea of relational storage is to group interesting pieces of data together, identifying it by a key value, and then relating it through the key to other interesting pieces of data.
Again, you may find, with really high data loads, that you need to partition your data, but even partitioning data, you put a mask on top of it so that it appears to be non-partitioned.
Let's imagine a business report that said, I want to see an aggregate of all sales in the state of Ohio, grouped by Company. With forty tables, you'd have a forty table UNION query joining against other tables over and over again, instead of a single query with a WHERE State = 'Ohio'
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 8, 2009 at 7:44 am
Of course it depends , but I would look lean towards the separate tables. A bit hairier to manage, but most likely easier data maintenance, and possibly performance. More importantly it would also allow easier customizations for a particular customer.
My last shop we had separate tables per customer in one database for the app we hosted, but eventually broke out each customer into it's own database, and then had one common db. Tho it made some management more complex, it was worth it for the flexibility.
Think thru all of the business and application requirements, and if that doesn't provide a clear answer load up a couple year's worth of test data and do some performance tests.
jg
January 8, 2009 at 7:56 am
Well, that is something different if you have different clients, not customers.
If you host your application to HMV and to VirginStore, there is a good reason to have the two clients customers separated. One database for HMV and HMV's customers and one database for VirginStore and VirginStore's customers. But inventory for all customers for client HMV should be in one table, and inventory for all customers for client VirginStore should be in one table.
OP wanted to know for his INVENTORY purposes if his customers should have one table each or not.
N 56°04'39.16"
E 12°55'05.25"
January 8, 2009 at 7:58 am
oops, you are right, I answered a different question...sorry
jg
January 8, 2009 at 8:48 am
Peso,
Does your script (full join) show the "Add", "Delete", "Change"?
In the delta report table, probably need one status column (its value can be like "Add","Delete", "change-Old", "change-New".
For the changed record, need to present the original value and the new value to customer, "change-old" will be for the original value,
"change-new" will be for the new value). How to generete the delta report in this way?
January 9, 2009 at 3:29 am
With all due respect to the OP, it sounds like a classical case of deciding to upgrade an old access/excel/whatever else solution to SQL Server, and it seems you are trying to do this without the necessary basic skills.
Whilst you might be able to get away with it (as SQL server will perform acceptably for smallish volumes of data, even if your design is horrendous), it will eventually catch up with you and maintenance and addition of new features will be impossible, as well as the inevitable performance degradation.
Take the time to read through the plethora of documentation available online - start with the SQL BOL library and the MSDN website, especially the patterns and practices section. When you are happy with the technology, then start to draw out the business model and the processes involved, and translate these to a database schema and other necessary components. For example, you may choose to use tables to store the information, stored procedures to add/remove/update information from the tables, views to provide snapshots of your tables (such as your inventory data) and Reporting Services to present these reports to users in a usable format.
As a general guide, your design should be as scalable as possible, meaning that if overnight you win 100 new customers all you have to do is add the data to the necessary tables, rather than amending your schema, adding tables, creating new queries, etc. Try to view the data (such as inventories, customer details, etc) as separate from the storage mechanism (the actual tables holding it), so that new data can be added without the data storage emchanism ahving to be amended.
Apologies for the lengthy post, but I am speaking from personal experience - I have just spend the best part of 12 months unpicking something similar!
All the best
S_Armondi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply