run sql queries under a particular schema context

Question

Hello Experts,

We are thinking about to create new schema with its own 3 tables which will be created on the fly for an individual customer.

To run a particular query for those tables in a procedure, should we have something like this.

declare @sName nvarchar(200);
select @sName =Schema_Name from schema where Schema_Id = passed_id_from_code
ALTER USER UserName WITH DEFAULT_SCHEMA = @sName 

-- Run the statements here --
...



-- After finishing executing statements
ALTER USER UserName WITH DEFAULT_SCHEMA = db;

In this scenario, can concurrent customers from various schema can update their own schema table or it will conflict.

Your suggestions are welcome. Anil

Answer 1

Most SQL databases have each table create as an unique entity in that database. That means that each table can be modified and altered individually with no relation to the other tables. CUSTOMERA.TABLE_ONE is a different object in the database that CUSTOMERB.TABLE_ONE. They do share the same name, but they are not the same object with potentially different layouts (as they have different schemas).

So unless there is some restriction on the RDBMS you can do this. Now having different schemas for each user may not be good. If you are develop the same app to handle several customers, you have to make sure it will work with all schemas and all custoemrs. In potentially different versions of the schema.

If you are going for a multi-tenant architecture, it may be wiser to use some kind of extension to to table. So you have a single DB.TABLE_ONE, with a CUSTOMER_DATA column where you put data in a know and flexible format (say JSON or XML). Some RDBMS have that that as a native features (I believe DB2 is one of them).

Hope this helps.