Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm using a multiple schema approach (Multi-Tenant arhitecture) in the same DB and it's working great. I can use EF bounded contexts and split domains appropriately.

According to MSDN, multi-tenant architecture can be a shared server (separate dbs), shared database (separate schema), or shared schema architecture.

Currently i'm writing TSQL to get the DB design i want but I would like to know if there is a way to choose the schema before creating a new table when using Database Diagram tool in SQL Management Studio?

share|improve this question
1  
A better multi-tenant approach IMHO is to have a separate database per client, rather than a separate schema. Management is about the same, but moving a client to its own server is easier, and you don't need a unique set of objects per client - in each database they can all be identical, they just contain different data. – Aaron Bertrand♦ Nov 29 '12 at 13:26
I understand. Using multiple schema is a better approach in my case. I can easily define security per schema and bounded context with EF. Thanks for your opinion. – plurby Nov 29 '12 at 13:36

2 Answers

up vote 1 down vote

Not really, in the diagrams created by Management Studio the tables will be created in the dbo schema. Even if the connected user has a different default schema, it will still create the tables in the dbo schema.

share|improve this answer
Thanks for confirming this. So there is no override or some other approach? – plurby Nov 29 '12 at 13:04
The best approach I see is to actually write your own scripts and not rely on the diagramming tool to generate them for you. If you really want to use an ERD tool, I think that you can check those of this list. But I really prefer to write every script myself.. :-). I could recommend you give a try to Power Designer, which is a great tool in itself, but I'm sure there are others. – Marian Nov 29 '12 at 13:11
I don't want to use other designer tools. For now i will investigate further and leave this question open. Thanks for replaying. – plurby Nov 29 '12 at 13:16
up vote 0 down vote

Database Diagrams is one of three Visual database tools.

After reading trough Visual Database Tools F1 Help, where there is no mention of choosing schema before creating a new table, which confirms that it automatically creates the table in the dbo schema.

To overcome this and still use Database Diagrams open your Table Properties window.

  1. Create new schema:

enter image description here

  1. Name your schema, define permissions etc.

enter image description here

  1. Change schema for your table while in design view using Properties window.

enter image description here

For me this approach is still faster that writing scripts, i didn't change to different designer tool and it works.

Hope it helps somebody.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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