Microsoft SQL Server
--
Questions
--
Followers
Top Experts
SQL Server 2008: Design table structures
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
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
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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.
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 ...
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 ...
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
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
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
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
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
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
thanks for this.
Microsoft SQL Server
--
Questions
--
Followers
Top Experts
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.