| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2011-01-05 : 08:33:42
|
| Hi all,Wish you have a very good day. I am working on the design of a Reporting System, where I need to fetch the updated information from existing database and then broadcast a subset of that information to different users as per their requirements. I need to track record of each and every broadcast and the details of the information which has been sent to the users in past. For example: I have the following set of information: Table: TableOneColumns: Col1,Col2,Col3,…,Col50Number of Rows: 10000List of users: User 1, User2, User 3Users may or may not have same requirements, so the information which needs to be sent to them could be “all, a sub set or none” of the information from the tableOne, with different combination of the columns from TableOne. Also, number of users can be increased and decreased.Recalling the requirements: <blue>“I need to track record of each and every broadcast along with the details of the information which has been sent to the users in past.”</blue>Following are the two approaches I have in my mind for the design of this system1) Create new tables (equals to the number of users) with the required columns (data columns along with DateCreated – where the current date will be stored)2) Introduce few columns into the TableOne e.g. [SentToUser1,DateSentToUser1], [SentToUser2,DateSentToUser2], … [ SentToUserN,DateSentToUserN]. So that every time a piece of information is sent to a user the corresponding columns should be updated with “Yes /No” and date information. Please let me know which of the above approach will be better <b>or I would appreciate if you guys can suggest a better design structure. </b>Many Thanks!MIK |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 11:58:24
|
| Why do you need to keep this record?Is it so that you can prove they have received the info or so that you don't send the same info again or something else?The answer to that will probably impact the design.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-05 : 12:07:11
|
| yes this is one of the main factors. Also this data will be required for reporting purposes ... e.g. for the decision making purposes |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 12:13:20
|
| >> yes this is one of the main factors.what is - I gave a couple of options.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-05 : 12:36:11
|
| :) pardon, i perceived them as same ... cause if i store a set of records, will tell me that the user(s) has/have received it and will not be sent the same info again ... Any how .. Yes, both of them are the reasons :) Many thanks! |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 12:51:36
|
| In that case you need to store the extracted data in the database - can't just keep a report image.Neither 1 nor 2 is good design.You could hold a single table with userid and the pk cols from TableOne.Then when you send the data add an entry for the user with all the PKs for the rows sent.Means you don't have to change structures when you add users.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-05 : 13:04:25
|
| This just came in to my mind too :) ... Many thanks and will let you know if i need any further help on this ...! |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-10 : 07:48:47
|
| hi there! Hope you will be having a good time :)With reference to the above i have now two flat tables (reflecting a denormalized form of existing DB). As you know that i need to send this information to a list of predefined users in excel form, which could be “all, a sub set or none” of the information from the tableOne, with different combination of the columns from the Flat tables. Well at this point I am interested in having some kind of structure which works as Template e.g. selection of the required columns from the respective table for each user. Actually i don't want to create a stored procedure every time when a new users comes in or to hard code this information at SP level. Can you please let me know if this could be done any way.Example: Say there are 100 columns in the table ... Col1,col2,col3,...,Col100User1 template: Col1,col2,col4,col10,col11,...,ColNUser2 template: Col2,col3,col4,col14,col15,...,ColN.and so on..Also, some of the users requires to title a column with the names as they requested .. e.g. I have a Column in the table with the name of Col2, but while sending this to user 1, it should be titled as "uniqueNumber" and for the other user it should be titled as "RowNumber" etc..Till now, i have 40 users list. Many thanks!! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-10 : 08:08:54
|
Do all the users get the exact same number of columns?You could have a table which has a structure likeUserName, COl1Name,Col2Name....etcYou could the populate the column names from that table as a select and union it to your output recordsSelect Username,Col1Name,Col2Name....etcFROM USERColumnsUNION ALLSelect Col1,Col2,Col3 etc...FROM DataResult would be similar to adding column names to a BCP queryout...perhaps something like that?then your solution for a new user would be simply adding a new row to the Usertable.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-12 : 07:29:31
|
| No, thats the point All User have different requirements and may or may not require the data in same format! Anyhow thanks I am going to try for some sort of fixed structure for the layouts of the reports and use them on run time... Will get back if i need any help over it Cheers! MIK |
|
|
|
|
Need advise over designing a system