|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Supporting user-defined "columns" with eye to SQLRS
allows for not-overly complicated methods for reporting. To show general requirements: a user or manager adds a few columns specific to his department, including "column" names. He then wants to create a report that includes these columns along with some of the default columns. Other users from other departments add other columns spefiic to themselves as well. I'm thinking that for the novice or regular users, setting up report models would suffice for column selection, while creating a table for all user defined types (key would be parent key plus department-key plus column-name key, maybe) would support holding the data, another for type info, but type enforcement and other issues are the things that I'm looking for at a best practice level. 1. Table that has one column of every type (with some max width for varchar, etc.) 2. Table has one data column and all data is serialized/stored as character data. 3. other. I don't want to alter tables because the same database may be used for multiple departments with different udc requirements, and I also want to minimize issues with data integrity. All of this has to be done with no DBA participation - the requirements include the creation and use of the user-defined data by the end-user. Any ideas? Thanks. > I don't want to alter tables because the same database may be used for So SELECT just only columns that your manager wants> multiple departments with different udc requirements, and I also want to > minimize issues with data integrity. All of this has to be done with no > DBA participation - the requirements include the creation and use of the > user-defined data by the end-user. > Any ideas? Show quoteHide quote "Stan Spotts" <sspotts@community.nospam> wrote in message news:OJ6K75DSGHA.1608@TK2MSFTNGP09.phx.gbl... > I'm looking for a best practice for creating user-defined columns that > allows for not-overly complicated methods for reporting. To show general > requirements: a user or manager adds a few columns specific to his > department, including "column" names. He then wants to create a report > that includes these columns along with some of the default columns. Other > users from other departments add other columns spefiic to themselves as > well. > > I'm thinking that for the novice or regular users, setting up report > models would suffice for column selection, while creating a table for all > user defined types (key would be parent key plus department-key plus > column-name key, maybe) would support holding the data, another for type > info, but type enforcement and other issues are the things that I'm > looking for at a best practice level. > > 1. Table that has one column of every type (with some max width for > varchar, etc.) > 2. Table has one data column and all data is serialized/stored as > character data. > 3. other. > > I don't want to alter tables because the same database may be used for > multiple departments with different udc requirements, and I also want to > minimize issues with data integrity. All of this has to be done with no > DBA participation - the requirements include the creation and use of the > user-defined data by the end-user. > Any ideas? > > Thanks. > Either I don't understand the solution in your short response, or you didn't
understand the issues. Do I need to expand with examples? I wasn't sure if I was clear enough in the requirements. Show quoteHide quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:elGEU8DSGHA.1844@TK2MSFTNGP12.phx.gbl... >> I don't want to alter tables because the same database may be used for >> multiple departments with different udc requirements, and I also want to >> minimize issues with data integrity. All of this has to be done with no >> DBA participation - the requirements include the creation and use of the >> user-defined data by the end-user. >> Any ideas? > > > So SELECT just only columns that your manager wants > > > > "Stan Spotts" <sspotts@community.nospam> wrote in message > news:OJ6K75DSGHA.1608@TK2MSFTNGP09.phx.gbl... >> I'm looking for a best practice for creating user-defined columns that >> allows for not-overly complicated methods for reporting. To show general >> requirements: a user or manager adds a few columns specific to his >> department, including "column" names. He then wants to create a report >> that includes these columns along with some of the default columns. >> Other users from other departments add other columns spefiic to >> themselves as well. >> >> I'm thinking that for the novice or regular users, setting up report >> models would suffice for column selection, while creating a table for all >> user defined types (key would be parent key plus department-key plus >> column-name key, maybe) would support holding the data, another for type >> info, but type enforcement and other issues are the things that I'm >> looking for at a best practice level. >> >> 1. Table that has one column of every type (with some max width for >> varchar, etc.) >> 2. Table has one data column and all data is serialized/stored as >> character data. >> 3. other. >> >> I don't want to alter tables because the same database may be used for >> multiple departments with different udc requirements, and I also want to >> minimize issues with data integrity. All of this has to be done with no >> DBA participation - the requirements include the creation and use of the >> user-defined data by the end-user. >> Any ideas? >> >> Thanks. >> > > Well, i understood your point, in my opinion it is really bad idea to allow
creating user-defined columns especially by end user Perhaps you can look at Reporting Services that provides/generates report by using .NET programming as well >>> department, including "column" names. He then wants to create a report That was exactly my point. Showing to each users only columns that tey >>> that includes these columns along with some of the default columns. >>> Other users from other departments add other columns spefiic to >>> themselves as well. wanted, but again only one person should be responsible for adding/altering db structure Show quoteHide quote "Stan Spotts" <sspotts@community.nospam> wrote in message news:OMA6d$DSGHA.4452@TK2MSFTNGP12.phx.gbl... > Either I don't understand the solution in your short response, or you > didn't understand the issues. Do I need to expand with examples? I > wasn't sure if I was clear enough in the requirements. > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:elGEU8DSGHA.1844@TK2MSFTNGP12.phx.gbl... >>> I don't want to alter tables because the same database may be used for >>> multiple departments with different udc requirements, and I also want to >>> minimize issues with data integrity. All of this has to be done with no >>> DBA participation - the requirements include the creation and use of the >>> user-defined data by the end-user. >>> Any ideas? >> >> >> So SELECT just only columns that your manager wants >> >> >> >> "Stan Spotts" <sspotts@community.nospam> wrote in message >> news:OJ6K75DSGHA.1608@TK2MSFTNGP09.phx.gbl... >>> I'm looking for a best practice for creating user-defined columns that >>> allows for not-overly complicated methods for reporting. To show >>> general requirements: a user or manager adds a few columns specific to >>> his department, including "column" names. He then wants to create a >>> report that includes these columns along with some of the default >>> columns. Other users from other departments add other columns spefiic to >>> themselves as well. >>> >>> I'm thinking that for the novice or regular users, setting up report >>> models would suffice for column selection, while creating a table for >>> all user defined types (key would be parent key plus department-key plus >>> column-name key, maybe) would support holding the data, another for type >>> info, but type enforcement and other issues are the things that I'm >>> looking for at a best practice level. >>> >>> 1. Table that has one column of every type (with some max width for >>> varchar, etc.) >>> 2. Table has one data column and all data is serialized/stored as >>> character data. >>> 3. other. >>> >>> I don't want to alter tables because the same database may be used for >>> multiple departments with different udc requirements, and I also want to >>> minimize issues with data integrity. All of this has to be done with no >>> DBA participation - the requirements include the creation and use of the >>> user-defined data by the end-user. >>> Any ideas? >>> >>> Thanks. >>> >> >> > > I'm well aware of the evils of letting users do this, but as in everything,
context matters. In this case, it has to be done. I've been looking at SQLRS as well as Report Builder (using report models to abstract the data), but there are other parts of the system where the data has to be used that need to be supported. I'll end up having to automate things like index generation, modification of views, etc., but I'm still looking for best practices about implementing this, not alternatives. I've already suggested numerous alternatives that were not accepted. :( Show quoteHide quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:%23hBgZEESGHA.5108@TK2MSFTNGP11.phx.gbl... > Well, i understood your point, in my opinion it is really bad idea to > allow creating user-defined columns especially by end user > Perhaps you can look at Reporting Services that provides/generates report > by using .NET programming as well > >>>> department, including "column" names. He then wants to create a report >>>> that includes these columns along with some of the default columns. >>>> Other users from other departments add other columns spefiic to >>>> themselves as well. > > > That was exactly my point. Showing to each users only columns that tey > wanted, but again only one person should be responsible for > adding/altering db structure > > > > "Stan Spotts" <sspotts@community.nospam> wrote in message > news:OMA6d$DSGHA.4452@TK2MSFTNGP12.phx.gbl... >> Either I don't understand the solution in your short response, or you >> didn't understand the issues. Do I need to expand with examples? I >> wasn't sure if I was clear enough in the requirements. >> >> "Uri Dimant" <u***@iscar.co.il> wrote in message >> news:elGEU8DSGHA.1844@TK2MSFTNGP12.phx.gbl... >>>> I don't want to alter tables because the same database may be used for >>>> multiple departments with different udc requirements, and I also want >>>> to minimize issues with data integrity. All of this has to be done >>>> with no DBA participation - the requirements include the creation and >>>> use of the user-defined data by the end-user. >>>> Any ideas? >>> >>> >>> So SELECT just only columns that your manager wants >>> >>> >>> >>> "Stan Spotts" <sspotts@community.nospam> wrote in message >>> news:OJ6K75DSGHA.1608@TK2MSFTNGP09.phx.gbl... >>>> I'm looking for a best practice for creating user-defined columns that >>>> allows for not-overly complicated methods for reporting. To show >>>> general requirements: a user or manager adds a few columns specific to >>>> his department, including "column" names. He then wants to create a >>>> report that includes these columns along with some of the default >>>> columns. Other users from other departments add other columns spefiic >>>> to themselves as well. >>>> >>>> I'm thinking that for the novice or regular users, setting up report >>>> models would suffice for column selection, while creating a table for >>>> all user defined types (key would be parent key plus department-key >>>> plus column-name key, maybe) would support holding the data, another >>>> for type info, but type enforcement and other issues are the things >>>> that I'm looking for at a best practice level. >>>> >>>> 1. Table that has one column of every type (with some max width for >>>> varchar, etc.) >>>> 2. Table has one data column and all data is serialized/stored as >>>> character data. >>>> 3. other. >>>> >>>> I don't want to alter tables because the same database may be used for >>>> multiple departments with different udc requirements, and I also want >>>> to minimize issues with data integrity. All of this has to be done >>>> with no DBA participation - the requirements include the creation and >>>> use of the user-defined data by the end-user. >>>> Any ideas? >>>> >>>> Thanks. >>>> >>> >>> >> >> > > go read about meta data.
let the users add rows, which get reported as columns. What Doug said. This functionality of adding "columns" can easily be
supported but you should re-design the db structure so that they are in essensce adding rows.  |
|||||||||||||||||||||||
Other interesting topics