none
Database shema, what for and when to use is ? RRS feed

  • Question

  • Dear all,

    I need to design a brand new database design and I have seen in different sample, like the NorthWind database, the use of database schema for some tables instead of default dbo.

    Based on that I have read different things around shema but sill not clear when and how to use. 

    I have read they are here mainly for table isolation or separation purpose ?

    I have read that you apply permission on shema instead of tables to avoid having to delete the user after affecting it direcly permission

    I have read also that when using shema, you MUST not have a foreign key of shema 1 refering a PK in shema 2 or reverse ?

    Does all of this is correct or are they other benefit on thinking to use schema in database architecture ?

    regards

    Sunday, July 22, 2018 9:20 PM

Answers

  • Hi wakefun,

    => I have read they are here mainly for table isolation or separation purpose ?

    Yes. A schema is a named container for database objects, which allows you to group objects into separate namespaces. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources.

    => I have read that you apply permission on schema instead of tables to avoid having to delete the user after affecting it directly permission.

    Yes. Check this: Schemas and Security 

    => I have read also that when using shema, you MUST not have a foreign key of shema 1 refering a PK in shema 2 or reverse ?

    No, I don't think so. Please refer to following sample, I have used AdventureWorks2016 sample database and its Production schema.

    select * from Production.Product
    
    create table dbo.test
    (
    TestID int not null,
    ProductID int,
    CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED (TestID),     
    CONSTRAINT FK_Test_Product FOREIGN KEY (ProductID) REFERENCES Production.Product (ProductID)
    )
    
    insert into dbo.test values
    (1,1),
    (2,2),
    (3,3),
    (4,4),
    (5,316)
    
    select * from dbo.test
    select * from Production.Product
    
    --drop table dbo.test

    As you can see I have created a new table which is using the default dbo schema and make it refer to the primary key of Production.Product (ProductID). Everything works fine.

    Reference: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 23, 2018 1:58 AM
    Moderator
  • Thnaks for the info.

    When using shema in database, is there any special thing to take in account when accessing database table through .NET ? do we need to specify the shema of the table we want to access to or is it fully transparent and only serve as authentication ?

    If I am planning to have web service hosted in NT services to access database and authentication to database is based on NT service account, does shema play a role in that case ?

    regards

    Its always better to prefix schema to table names (especially when there are multiple schemas in the dbs involved with same objects existing in each). This is usually referred to as two part naming 

    If you didnt specify the schema, optimizer tries to resolve it to your default schema first. So in case if the same object exists in  multiple schemas and if attempt was to get data from table in some other schema which you had access to, you will end up getting data from the wrong object.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 23, 2018 6:50 AM
    Moderator
  • Thnaks for the info.

    When using shema in database, is there any special thing to take in account when accessing database table through .NET ? do we need to specify the shema of the table we want to access to or is it fully transparent and only serve as authentication ?

    If I am planning to have web service hosted in NT services to access database and authentication to database is based on NT service account, does shcema play a role in that case ?

    regards

    Hi wakefun,

    The schema is used to manage the objects in database. If you mean to access the database, I don't think schema is playing a role in it.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 23, 2018 7:38 AM
    Moderator
  • I do agree that the definition of what a schema is is confusing. One problem is that people often use terms improperly.

    A schema is a definition of a database and/or tables. A schema can define many other things too, such as XML (see Schema - W3C). See the following for descriptions of database schemas.

    As I said, people tend to use terms in ways that vary from their intended purpose. For example "SQL" is a language but "SQL Server" is very different; it is a software product, a database system. You need to be careful because you seem to be reading material that uses "schema" in a manner inconsistent with the original and common use. You say "apply permission on shema instead of tables" that is confusing for me. That does not seem to be a schema in the original and common definition.



    Sam Hobbs
    SimpleSamples.Info

    Tuesday, July 24, 2018 1:12 AM

All replies

  • Hi wakefun,

    => I have read they are here mainly for table isolation or separation purpose ?

    Yes. A schema is a named container for database objects, which allows you to group objects into separate namespaces. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources.

    => I have read that you apply permission on schema instead of tables to avoid having to delete the user after affecting it directly permission.

    Yes. Check this: Schemas and Security 

    => I have read also that when using shema, you MUST not have a foreign key of shema 1 refering a PK in shema 2 or reverse ?

    No, I don't think so. Please refer to following sample, I have used AdventureWorks2016 sample database and its Production schema.

    select * from Production.Product
    
    create table dbo.test
    (
    TestID int not null,
    ProductID int,
    CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED (TestID),     
    CONSTRAINT FK_Test_Product FOREIGN KEY (ProductID) REFERENCES Production.Product (ProductID)
    )
    
    insert into dbo.test values
    (1,1),
    (2,2),
    (3,3),
    (4,4),
    (5,316)
    
    select * from dbo.test
    select * from Production.Product
    
    --drop table dbo.test

    As you can see I have created a new table which is using the default dbo schema and make it refer to the primary key of Production.Product (ProductID). Everything works fine.

    Reference: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 23, 2018 1:58 AM
    Moderator
  • Thnaks for the info.

    When using shema in database, is there any special thing to take in account when accessing database table through .NET ? do we need to specify the shema of the table we want to access to or is it fully transparent and only serve as authentication ?

    If I am planning to have web service hosted in NT services to access database and authentication to database is based on NT service account, does shema play a role in that case ?

    regards

    Monday, July 23, 2018 6:39 AM
  • Thnaks for the info.

    When using shema in database, is there any special thing to take in account when accessing database table through .NET ? do we need to specify the shema of the table we want to access to or is it fully transparent and only serve as authentication ?

    If I am planning to have web service hosted in NT services to access database and authentication to database is based on NT service account, does shema play a role in that case ?

    regards

    Its always better to prefix schema to table names (especially when there are multiple schemas in the dbs involved with same objects existing in each). This is usually referred to as two part naming 

    If you didnt specify the schema, optimizer tries to resolve it to your default schema first. So in case if the same object exists in  multiple schemas and if attempt was to get data from table in some other schema which you had access to, you will end up getting data from the wrong object.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 23, 2018 6:50 AM
    Moderator
  • Thnaks for the info.

    When using shema in database, is there any special thing to take in account when accessing database table through .NET ? do we need to specify the shema of the table we want to access to or is it fully transparent and only serve as authentication ?

    If I am planning to have web service hosted in NT services to access database and authentication to database is based on NT service account, does shcema play a role in that case ?

    regards

    Hi wakefun,

    The schema is used to manage the objects in database. If you mean to access the database, I don't think schema is playing a role in it.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 23, 2018 7:38 AM
    Moderator
  • I do agree that the definition of what a schema is is confusing. One problem is that people often use terms improperly.

    A schema is a definition of a database and/or tables. A schema can define many other things too, such as XML (see Schema - W3C). See the following for descriptions of database schemas.

    As I said, people tend to use terms in ways that vary from their intended purpose. For example "SQL" is a language but "SQL Server" is very different; it is a software product, a database system. You need to be careful because you seem to be reading material that uses "schema" in a manner inconsistent with the original and common use. You say "apply permission on shema instead of tables" that is confusing for me. That does not seem to be a schema in the original and common definition.



    Sam Hobbs
    SimpleSamples.Info

    Tuesday, July 24, 2018 1:12 AM