October 7, 2009 at 3:43 pm
Hi All,
I have a requirement allowing users to defined their own tables through a front end application! Basically what I'm looking for is a sort of best practice with regards to this. Should I physically create the table in the database, or should I have a "tables" table that houses the created tables. Any suggestions would be greatly received.
Thanks,
R.
October 7, 2009 at 3:47 pm
Really need more information regarding what you are attempting to accomplish. Why do the users need to define their own tables?
October 7, 2009 at 3:51 pm
We're creating a highly customisable application, which facilitates users being able to extend the existing functionality and/or store information specific to just themselves. Obviously it will only be admin users that will be able to perform this sort of functionality.
October 7, 2009 at 4:14 pm
Allowing users to create their own tables is almost like open Pandoras box...
Once they can create a (simple) table the next requests most probably will be:
- design complex tables using constraints, computed columns a.s.o.
- being able to define foreign key relationship (maybe even with cascading integrity)
- create/alter trigger
- create views using tables designed by "others" - which will open up the wide field of security and permissions
- the permission issue will be one of the first things you need to consider once you decide to follow that path...
I'd recommend not to provide this functionality through an application. I'd rather recommend to have a dedicated person/group (DBA?) that'll take care of such requests. This will also help to ensure data integrity (How would you avoid having two user creating the same table with a slightly different name, each table filled up with almost identical data?)
If this functionality will be limited to admin users only, why not design a database role for them and let them work with SSMS?
Like I said: Pandoras box...
October 7, 2009 at 4:42 pm
Hi Lutz,
I hear ya, but unfortunately it is a business requirement that we are required to implement (against the better judgement some, and including the points that you mentioned above), in saying that, that's why I'm wondering if I should physically create those tables as opposed to creating a "tables" table and a "columns" table and storing the information in those.
As it stands it's envisaged that Admin users will be fairly knowledgeable with regards to database design (indeed, it's something that we demand), and obviously we will provide some sort of deduplication with regards to table names etc.
So, ignoring that fact that this is a support nightmare (amongst other things) - what would be the best way to implement this?
Thanks,
Rowan.
October 7, 2009 at 4:59 pm
Ruaghain (10/7/2009)
As it stands it's envisaged that Admin users will be fairly knowledgeable with regards to database design (indeed, it's something that we demand), and obviously we will provide some sort of deduplication with regards to table names etc.Thanks,
Rowan.
Assuming there will be "fairly knowledgeable admin", why not providing an editor window where they can write the DDL script? Submit that script to a sproc, log the script, user and time in a separate table and run the code they entered. Since this would make your database basically wide open for SQL injection you should strictly limit permissions for the user being allowed to run the sproc, especially in terms of alter/drop tables not created by that user/login. Maybe you should consider using separate schemas for your and "their" data...
I just hope you've got an excellent support contract with a huge nightmare-add-on....
Btw: it might be well worth it to question the business requirement since it either put the database integrity at risk or will add a significant effort to the project/budget.
October 7, 2009 at 7:37 pm
Ruaghain (10/7/2009)
Hi Lutz,I hear ya, but unfortunately it is a business requirement that we are required to implement (against the better judgement some, and including the points that you mentioned above), in saying that, that's why I'm wondering if I should physically create those tables as opposed to creating a "tables" table and a "columns" table and storing the information in those.
As it stands it's envisaged that Admin users will be fairly knowledgeable with regards to database design (indeed, it's something that we demand), and obviously we will provide some sort of deduplication with regards to table names etc.
So, ignoring that fact that this is a support nightmare (amongst other things) - what would be the best way to implement this?
Thanks,
Rowan.
If all of that is true, why redevelop the wheel? Why not just give them access through SSMS? They'll probably figure that out on their own, anyway...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply