March 6, 2012 at 9:16 am
Eugene Elutin (3/6/2012)
I would go with Eric M Russell's suggestion:Kill the project!
Give your users MS Access if they want to abe able to create tables themselves.
Unless we're talking about a hosted database provider, I totally don't understand the need to let users create and manage their own tables.
Now, allowing users to enter their own metadata (lookup codes / descriptions, etc.) through the application, that's a different story. For example, many years ago I developed an artifact classification database application for a museum, and the users could enter ad-hoc attribute keys like "oval shaped" or "brass".
Also, BI tools like SQL Server Reporting Services provide tools that allow users to create their own "Reporting Models", or high level logical views of the tables.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 6, 2012 at 9:43 am
Eric M Russell (3/6/2012)
Eugene Elutin (3/6/2012)
I would go with Eric M Russell's suggestion:Kill the project!
Give your users MS Access if they want to abe able to create tables themselves.
Unless we're talking about a hosted database provider, I totally don't understand the need to let users create and manage their own tables.
Now, allowing users to enter their own metadata (lookup codes / descriptions, etc.) through the application, that's a different story. For example, many years ago I developed an artifact classification database application for a museum, and the users could enter ad-hoc attribute keys like "oval shaped" or "brass".
Also, BI tools like SQL Server Reporting Services provide tools that allow users to create their own "Reporting Models", or high level logical views of the tables.
I guess it may be the classical case of BA "broken phone" requirements...
I would ask so many questions to the solution in 5 minutes:
How users are going to create tables? Via SSMS? What about licensing cost? Writing DDL statements? Are your users SQL developers? How you going to validate DDL doesn't destroy anything? Are you going to implement a special UI? if yes, question to the project sponsor - is he willing to pay (a lot) for something he already paid for when he paid for SQLServer?
Testing? Do you have all testers turned from SQL developers to test application which allows to create SQL objects?
It's clearly something wrong here...
I am with Eric again:
Creating own metadata - fine!
Creating own Report Models (including views) - fine!
But the real tables? What about if your user will issue something like:
SELECT s.* INTO MyTable FROM sys.columns s1, sys.columns s2, sys.columns s3, sys.columns s4 ...
Just to find out what the space is left on a drive :hehe:
March 6, 2012 at 9:51 am
i wouldn't want to tackle this myself.
But, as sort of a hybrid approach, if you could at least provide a skeleton of the tables and add customizable columns to the tables, that would be fine although it makes the programming more difficult. (Of course the data types would be static, but provide some numeric and some character type and maybe date columns.) But that would be much better than letting them create their own tables.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 6, 2012 at 10:07 am
mtillman-921105 (3/6/2012)
i wouldn't want to tackle this myself.But, as sort of a hybrid approach, if you could at least provide a skeleton of the tables and add customizable columns to the tables, that would be fine although it makes the programming more difficult. (Of course the data types would be static, but provide some numeric and some character type and maybe date columns.) But that would be much better than letting them create their own tables.
That's exactly what I would do. Have as many tables with as many columns as you see allowable that are empty, but have definitions. Then, map those tables to another table with the definitions that the user defines. Something like this:
CREATE TABLE UserDefinedVC1 (id int identity(1,1), custom1 VARCHAR(N), ... customN VARCHAR(N));
CREATE TABLE UserDefinedMappings (tableName, tableColumnName, userDefinedTableName, userDefinedColumnName);
Something like this. This of course is a quick thought and not really defined (brainstorming). However, something "like" this may be good because it gives you control over the database, but gives the customer customizable options.
Jared
CE - Microsoft
March 6, 2012 at 10:23 am
agustingarzon (2/29/2012)
...This would be fool proof, and monitored by DBAs for performance problems, but this is just a small project.
...
I find this part the toughest. There really is no such thing as fool proof. Fools seem to be geniuses at breaking things.
March 6, 2012 at 10:36 am
Lynn Pettis (3/6/2012)
agustingarzon (2/29/2012)
...This would be fool proof, and monitored by DBAs for performance problems, but this is just a small project.
...
I find this part the toughest. There really is no such thing as fool proof. Fools seem to be geniuses at breaking things.
Just dump it all in a separate virtual instance, and call it a playpen.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 6, 2012 at 5:38 pm
mtillman-921105 (3/6/2012)
i wouldn't want to tackle this myself.But, as sort of a hybrid approach, if you could at least provide a skeleton of the tables and add customizable columns to the tables, that would be fine although it makes the programming more difficult. (Of course the data types would be static, but provide some numeric and some character type and maybe date columns.) But that would be much better than letting them create their own tables.
This is pretty much the idea. The programming is not a problem. The users would pick the data types from a few available options (like some one might have mentioned earlier).
The amount of columns would be limited. And it would only be available to a reduced amount of people. Direct sql injection is not going to be an option.
March 7, 2012 at 7:50 am
agustingarzon (3/6/2012)
mtillman-921105 (3/6/2012)
i wouldn't want to tackle this myself.But, as sort of a hybrid approach, if you could at least provide a skeleton of the tables and add customizable columns to the tables, that would be fine although it makes the programming more difficult. (Of course the data types would be static, but provide some numeric and some character type and maybe date columns.) But that would be much better than letting them create their own tables.
This is pretty much the idea. The programming is not a problem. The users would pick the data types from a few available options (like some one might have mentioned earlier).
The amount of columns would be limited. And it would only be available to a reduced amount of people. Direct sql injection is not going to be an option.
That's how I would handle it if I were you. I work with two applications that have extra, empty columns in tables that are for that purpose. 'Not a bad idea since every company who uses the software will have its own special business rules and idiosyncrisies. It's a good way to build in flexibility.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 7, 2012 at 11:42 am
mtillman-921105 (3/7/2012)
agustingarzon (3/6/2012)
mtillman-921105 (3/6/2012)
i wouldn't want to tackle this myself.But, as sort of a hybrid approach, if you could at least provide a skeleton of the tables and add customizable columns to the tables, that would be fine although it makes the programming more difficult. (Of course the data types would be static, but provide some numeric and some character type and maybe date columns.) But that would be much better than letting them create their own tables.
This is pretty much the idea. The programming is not a problem. The users would pick the data types from a few available options (like some one might have mentioned earlier).
The amount of columns would be limited. And it would only be available to a reduced amount of people. Direct sql injection is not going to be an option.
That's how I would handle it if I were you. I work with two applications that have extra, empty columns in tables that are for that purpose. 'Not a bad idea since every company who uses the software will have its own special business rules and idiosyncrisies. It's a good way to build in flexibility.
It's like you stole the line from our minds 😉
Special business rules and idiosyncrasies...
March 7, 2012 at 12:00 pm
agustingarzon (3/6/2012)
... The users would pick the data types from a few available options (like some one might have mentioned earlier).The amount of columns would be limited. And it would only be available to a reduced amount of people. Direct sql injection is not going to be an option.
You might be surprised at what an enterprising user can end up building.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply