?
Solved

SQL Server 2008: Design table structures

Posted on 2013-01-02
6
Medium Priority
?
604 Views
Last Modified: 2013-02-06
Hello,

I would like to design database structure on which could you please give your greater thoughts as per below requirements?

I have a report which will be scheduled nightly and when end user will come up in the morning then program needs to fetch data only from calculated stored tables; there will not be any calculation from other transaction tables.

For e.g., I've a two dropdown list / combo box i.e. Department and Salary with multi select option. (Note: Department and Salary from other master tables which are also having internal ID in database like HR - 1, Sales -2 and similar 0 to 10000 - 101 on so on.

Now, user can select one/multiple item(s) from Department and Salary as well.

Department      Salary
HR                      0 to 10000
Sales              10000 to 15000
Account              15000 to 20000
Market              20000 Above

Questions:
1. What should be the best structure design like we have in OLAP?
2. We need to store calculated result nightly based on all permutation & combination, do you have any thoughts?
3. How we can generate a key to fetch calculated data?


Best Regards,
Mohit Pandit
0
Comment
Question by:MohitPandit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 79

Expert Comment

by:arnold
ID: 38738851
Are you talking about installing sql server reporting services where you can design reports to get the data you want from the data stored in the database.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38739159
Well for that we will be required complete information that how your source tables holding data and what you required to show in report...

But one suggestion can be ...
You are running your process over night so do every calculation with each permutation & combination and store them in a flat table...
In morning Report will required only to fetch and show that data...

And for the key you can define as per your permutation ...
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 38772061
Hi Saurv,

Yes, it will be nightly process and in the morning on fetch. You caught correctly.

You may take same example aforesaid for department and salary. Kindly let me know in case you need more information.

Best Regards,
Mohit Pandit
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 5

Author Comment

by:MohitPandit
ID: 38780790
Hi,

I've done it myself. I stored the combination in separate table let's say 'LookUp' along with 'Internal_Id' and in another table I store a key with combination in another table.

For e.g. first, I've only three values in two dropdownlist as below:
Note: we will be having default value "Select All" so that means 4 values instead of 3 in dropdown.

Department      Salary
HR (1)      0 to 10000 (1)
Sales (2)      10001 to 20000 (2)
Market (3)      20001 Above (3)

So, second, below table of combinations (as lookup):
As you can see 9 combination because we will be having other value "Select All" and NULL values shows user select all
Internal_Id      Department      Salary
1      1      1
2      1      2
3      1      3
4      2      1
5      2      2
6      2      3
7      3      1
8      3      2
9      3      3
10      NULL      1
11      NULL      2
12      NULL      3
13      1      NULL
14      2      NULL
15      3      NULL
16      NULL      NULL


and third, the Internal_Id column will be used to store actual result values in other result table.
Like I aforesaid 2nd point, all combination key value from 1 to 16 and it will also be having combination like 1,2 (if end user selects department = 1 and salary = 1 and 2) then internal id will match for two rows and internal id will be 1 & 2. So, it will be store in comma separated with in result table.

Now, one challenge seems, the comma separated key combination of internal_id column.

Could you please look into it and give your great thoughts?

Best Regards,
Mohit Pandit
0
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 1500 total points
ID: 38781679
Hi Mohit,
Besides saving comma separated internal id's I will suggest different logic.....
First look at the below code and execute in SSMS...
Declare @Result_set table (internal_id varchar(10), col1 varchar(max),col2 varchar(max) )
Declare @All_Combinatios table (Internal_Id int,      Department smallint,      Salary smallint )


insert into @All_Combinatios 
 select  power(2,1) ,      1 ,     1
union all select  power(2,2),      1  ,    2
union all select  power(2,3),      1   ,   3
union all select  power(2,4),      2   ,   1
union all select  power(2,5),      2   ,   2
union all select  power(2,6),      2   ,   3
union all select  power(2,7),      3   ,   1
union all select  power(2,8),      3   ,   2
union all select  power(2,9),      3   ,   3
union all select  power(2,10),      NULL,      1
union all select  power(2,11),      NULL ,     2
union all select  power(2,12),      NULL ,     3
union all select  power(2,13),      1   ,   NULL
union all select  power(2,14),      2    ,  NULL
union all select  power(2,15),      3     , NULL
union all select  power(2,16),      NULL   ,   NULL

select * from @All_Combinatios 

insert into @Result_set 
select '2' , 'Caluculated_Field1','Caluculated_Field2' union all 
select '6' , 'Caluculated_Field3','Caluculated_Field4' union all 
select '14'  , 'Caluculated_Field1','Caluculated_Field2'  union all 
select '16' , 'Caluculated_Field1','Caluculated_Field2' union all 
select '48' , 'Caluculated_Field11','Caluculated_Field12' 

select * from @Result_set 

Open in new window


Now you see in @All_Combinatios we have Internal_id's in power of two... If you will add any of the internal id you will get the unique value.. for example addition of  Internal_id (2,4) will produce 6, that is unique value and does't exists in @All_Combinatios..

Now in final result set table if you want to store the result for Department 1 and salary ( 1,2) ... then you can store the result with internal_id 6 ......

Overall logic is Value of Internal_id or sum of any combination will be unique...

hope you understood...
you can also do string comparison with comma separated but it is not a good idea..

let me know if you have concerns...

Thanks,
Saurabh
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 38858791
thanks for this.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question