February 29, 2012 at 11:56 am
We are going to develop a project where clients will be able to Create their own SQL tables and fields.
The idea is to give the client the possibility to shape the tables to their business type, and then fill it up with content.
Initially, we thought we would create one table for the "tables definition", a second table for the "fields definition", and a third table for the contents itself.
The problem with this approach is that it would become difficult to manage the data, we would have to join the different tables and this would probably be overkill.
The second idea was creating actual SQL tables and actual SQL fields under a different schema. But the thing about this is that the database would probably get too cluttered with tables created by the users, and this database also hosts other tables that we need for the system to work.
The third idea was tossing all these user tables to a second database, so it wouldn't matter how cluttered it is, but the problem would be "relating" the tables on the second database with tables on the first database.
So, how would you suggest to tackle this ? The option two sounds like the way to go, but there may be a different approach I could be missing.
Cordially, Agustín 🙂
February 29, 2012 at 12:23 pm
Hey there, I would go with the 3rd option. The users would still be able to reference the tables on the original DB through a linked server connection, but they'd also have their own area to work with
February 29, 2012 at 12:26 pm
If users will be allowed to create tables, they should know how to design a database (e.g. build an ERM based on business requirements).
The issue with a separate schema vs. a separate database is almost identical in terms of joining those tables to to the "original" schema/database: who'll be the one to define those joins? The user? A separate DBA? Once this is defined, the rest is more or less setting up the permissions. A separate database would have the advantange that your original db isn't directly affected by any major error (are the users allowed to drop tables, too?) that might require a restore from backup.
Question aside: What "front end" will the project be based on? It sounds like it's not only an issue of allowing the users to create their own tables, you'll need an application layer that can be modified, too. Sounds like an Access frontend to me...
February 29, 2012 at 12:29 pm
"We are going to develop a project where clients will be able to Create their own SQL tables and fields. "
Do you currently provide your clients with some form of business processing based on a gui/database...??
do the proposed "create you own" table need to integrate into exg processes?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 29, 2012 at 12:44 pm
guerillaunit: Will investigate the linked server suggestion. Option 3 would be the best if we were able to join to the primary database.
LutzM: What we would be offering would be an over simplified approach. It would allow the creating of a limited set of tables which would obtain an identity field automatically, the users would be able to create and define a few fields, and also set a limited amount of indexes.
The frond end would be a web application, then again, it will be something a lot simpler, not nearly as close as an Access front end.
This would be fool proof, and monitored by DBAs for performance problems, but this is just a small project.
The relationships would probably involve just one foreing key per table that would be created automatically (and not editable), referencing a table on the primary database (if this type of relationship were possible)
So, knowing this, what would you suggest ?
Thanks for your suggestions :: )
Cordially, Agustin.
February 29, 2012 at 12:47 pm
Both databases would be on the same SQL Server instance, in case I forgot to mention :unsure:
February 29, 2012 at 2:23 pm
agustingarzon (2/29/2012)
...So, knowing this, what would you suggest ?
I would use a web site that would allow to define a table name and a list of columns with data type assigned based on a drop down list, where the identiy column would already be predefined and not editable. This web site would of course refuse any special character to prevent SQL injection right at the beginning (e.g. no space, no character sign).
I would then pass this parameter to a stored procedure, perform data validation and create a dynamic sql statement using sp_executesql. The result (success or error) would be returned to the app.
February 29, 2012 at 4:31 pm
Is EAV an option? The users wouldn't actually be creating tables, they would be creating metadata about their 'tables'. The metadata and the actual data could be stored in the existing tables.
http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model
There is a lot of valid criticism about this approach but your situation is quite unique.
March 1, 2012 at 6:59 am
Chrissy321, I'm reviewing your suggestion now.
LutzM: I meant what would be your suggestion SQL wise, e.g. using the same database, using a schema, a second database, or a third approach.
If I were to use a second database on the same instance, would it be possible to create table relationships, or joining queries, and so on.
Later guys !
March 1, 2012 at 11:53 am
I would use a separate database just to make it easier for backup and restore.
A join would simply be
SELECT t1.col1, t2.col2
FROM table1 t1 INNER JOIN YourOtherDatabase.Schema.Table2 t2 ON t1.col1=t2.col2
As long as both databases are on the same SQL instance, there shouldn't be any major performance degrade (if any at all).
March 1, 2012 at 8:01 pm
agustingarzon (2/29/2012)
Both databases would be on the same SQL Server instance, in case I forgot to mention :unsure:
Then, no need for a linked server. Create a wad of SYNONYMs that point to the tables in the first database and grant SELECT only on those synonyms. The users don't even have to know about the first database.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2012 at 11:20 am
Honored with your suggestion, Jeff 😉
Thanks guys, I thought there would some difficulties crossing the database boundaries but with your suggestions it seems that this could work seamlessly.
Best regards, Agustin.
March 5, 2012 at 7:48 am
agustingarzon (2/29/2012)
We are going to develop a project where clients will be able to Create their own SQL tables and fields.The idea is to give the client the possibility to shape the tables to their business type, and then fill it up with content.
Initially, we thought we would create one table for the "tables definition", a second table for the "fields definition", and a third table for the contents itself.
The problem with this approach is that it would become difficult to manage the data, we would have to join the different tables and this would probably be overkill.
The second idea was creating actual SQL tables and actual SQL fields under a different schema. But the thing about this is that the database would probably get too cluttered with tables created by the users, and this database also hosts other tables that we need for the system to work.
The third idea was tossing all these user tables to a second database, so it wouldn't matter how cluttered it is, but the problem would be "relating" the tables on the second database with tables on the first database.
So, how would you suggest to tackle this ? The option two sounds like the way to go, but there may be a different approach I could be missing.
Cordially, Agustín 🙂
If killing the project is an option, then I'd suggest that as the best approach. Otherwise, keep all these objects in a separate database so at least the chaos is contained.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 6, 2012 at 8:41 am
agustingarzon (3/4/2012)
Honored with your suggestion, Jeff 😉Thanks guys, I thought there would some difficulties crossing the database boundaries but with your suggestions it seems that this could work seamlessly.
Best regards, Agustin.
No, not really; cross-database work on the same instance doesn't have many issues, though it does have some. I agree; keep them in their own database, and use Jeff's SYNONYM idea if you like.
Be aware that once they know about this, they may end up adding even more databases later; if possible, restrict their permissions to the objects you don't want them to change before you roll this out.
Off the top of my head:
As was said before, use 3 part naming.
One more database for backups and restore tests.
One more database for index maintenance.
One more database for capacity planning, along with more slack drive space; now both of them have to have log files and slack space.
Backups and restores are taken at different times; and even a PITR (Point In Time Restore) isn't guaranteed to get them exactly in alignment again; that, I've read, is what STOPATMARK and STOPBEFOREMARK are for. That said, synchronized PITR is probably more than good enough for most uses; many users continue to use SIMPLE recovery mode and "within a couple hours of each other" backups and restores. Just consider your own business requirements carefully.
Database level permissions obviously get a little more complex; granting EXECUTE to SP_1 in DB_A doesn't, in and of itself, grant the user rights to any objects in DB_B that SP_1 requires. There are various and sundry ways around this, from granting the user rights to the tables in DB_B that SP_1 uses, to signing SP_1 and granting the credentials used for the signature those rights (and re-signing SP_1 after every change, of course).
Be aware of what physical spindles are used by each DB's database and log files, and what gets to share with what.
March 6, 2012 at 9:02 am
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.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply