1

Database schema helps grouping tables, or help identify multiple tables that have the same table name. Most of the schema usage that I have found so far are for grouping tables, isolating tables among the users (or talents), allow different permission based on the schema.

In my case, I have a predefined table definition, and I like my user to be able to duplicate this table multiple times in my application. Obviously, I can't have the user create the new tables with the same name. My original idea is to allow the user specify a table name suffix:

e.g.
[TableA]
[CustomName_TableA]
[OtherName_TableA]

However, this requires a bit of code change since the table name is hardcoded in the data access layer of the application.

What are the drawbacks, or potential issues if I create a new schema when a user duplicate the table, instead of dynamically generate a new table name?

e.g.
[dbo].[TableA]
[CustomName].[TableA]
[OtherName].[TableA]
Improve this question
1
  • Perhaps look into using temporary tables as a compromise?
    – Andrew Brennan
    Commented Sep 1, 2016 at 8:26

2 Answers 2

Reset to default
1

Please elaborate more on your use case. The solutions I can think of are: 1- Using a prefix for the table to be used by the table name (username_TABLE)

2- using Temporary tables then exporting the review into a new table (then you would add a new column to the table and the content of that column will be the username): Example fields: USERID, COLUMN1, COLUMN2, etc.

3- Using a view. Using a view, you could actually use the same "table" name (applications don't know the difference between a view/table), then again you could use an extra column in your underlying table.

Improve this answer
2
  • My team is building a DataMart solution, and one feature we like to have is the ability to duplicate a ETL process. However, the ETL application is built on a codebase which has statically hardcoded table definition. We are considering making major change to the data access layer so that it can dynamically generate table definition which would prefix the table (your solution 1). However, we like to explore other options since the idea I mention require a lot of risky code changes.
    – dsum
    Commented Sep 1, 2016 at 22:00
  • Decided using a view with an extra column in the table is way better than using schema for my scenario.
    – dsum
    Commented Sep 7, 2016 at 18:13
1

I really disagree with your statement that "database schema helps grouping tables, or help identify multiple tables that have the same table name." Actually database schemas were originally created to address security issues and security groupings. Unfortunately, in all of the worksplaces and projects I've been involved with, schema is synonymous with namesspaces (group names). This is just bad design in my opinion.

You want to create a new schema for every user, but how many users will there be? You don't want 1000 schemas in your database. Schemas are registered in the metadata as unique objects, and there is a limit 2,147,483,640 - they are considered objects and the more objects you have the higher the overhead. The limits are set based on the SQL product you use. The number I gave was for SQL Server

Improve this answer
1
  • Originally, I was would have agreed with you that its purpose was design for security grouping. However, after reading a number of posts online, it appears there are more scenarios you can use database schema. stackoverflow.com/questions/2674222/…
    – dsum
    Commented Sep 1, 2016 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.