I am creating a database with about 40 different tables.
我正在创建一个包含大约40个不同表的数据库。
I have heard about people grouping tables into database 'schemas' - what are the implications of using different schemas in a database? Can tables from one schema still relate to another schema? What are the functional differences between different schemas?
我听说有人将表分组到数据库'schemas'中 - 在数据库中使用不同模式有什么含义?一个模式中的表是否仍然与另一个模式相关?不同模式之间的功能差异是什么?
3 个解决方案
#1
1
Where are schemas located in SSMS? They are rightfully placed under the security tab.
SSMS中的模式在哪里?它们被合理地放置在安全选项卡下。
Lets use the AdventureWorks databases.
让我们使用AdventureWorks数据库。
If you assign security at the schema level, purchasing users will only have access to the purchasing table and sales people will have only access to the sales tables.
如果您在架构级别分配安全性,则购买用户只能访问购买表,销售人员只能访问销售表。
In fact, they will not even see the other tables if you set it up correctly.
事实上,如果你正确设置,他们甚至不会看到其他表格。
If you combine schemas with creating tables/indexes on file groups, now you can place all the sales people onto file group sales and purchasing on file group purchasing.
如果将模式与在文件组上创建表/索引相结合,现在可以将所有销售人员放在文件组销售和采购文件组上。
IE - Spreading the I/O load.
IE - 传播I / O负载。
In short, I think schemas are an unknown and little used feature.
简而言之,我认为模式是一个未知的,很少使用的功能。
Check out my blog article on this fact.
看看我关于这个事实的博客文章。
#2
1
I assume that you are talking about SQL Server. You can join and reference between tables in different schemas. I see it mostly used for visual organization and/or for managing objects' permission (you can assign permissions at the schema-level).
我假设你在谈论SQL Server。您可以在不同模式中的表之间进行连接和引用。我发现它主要用于可视化组织和/或管理对象的权限(您可以在模式级别分配权限)。
If you are worried about any negative effects of doing dbo.table vs custom.table - there aren't any that I imagine you would encounter.
如果你担心做dbo.table与custom.table的任何负面影响 - 我想不到你会遇到任何负面影响。
#3
1
Schemas are just collections of database objects. They are useful for maintaining separation of sets of objects.
模式只是数据库对象的集合。它们对于保持对象集的分离很有用。
There is always at least one schema. For SQL Server it is named dbo.
始终至少有一个架构。对于SQL Server,它名为dbo。
One implication of having multiple schemas is that you will have to manage permissions for the various schemas. This is usually done via a role that's associated with the schema.
拥有多个模式的一个含义是您必须管理各种模式的权限。这通常通过与架构关联的角色完成。
Objects in one schema are available to objects from another, and there is no performance penalty in writing queries that use objects from multiple schemas.
一个模式中的对象可用于另一个模式中的对象,并且在编写使用来自多个模式的对象的查询时不会有性能损失。
#1
1
Where are schemas located in SSMS? They are rightfully placed under the security tab.
SSMS中的模式在哪里?它们被合理地放置在安全选项卡下。
Lets use the AdventureWorks databases.
让我们使用AdventureWorks数据库。
If you assign security at the schema level, purchasing users will only have access to the purchasing table and sales people will have only access to the sales tables.
如果您在架构级别分配安全性,则购买用户只能访问购买表,销售人员只能访问销售表。
In fact, they will not even see the other tables if you set it up correctly.
事实上,如果你正确设置,他们甚至不会看到其他表格。
If you combine schemas with creating tables/indexes on file groups, now you can place all the sales people onto file group sales and purchasing on file group purchasing.
如果将模式与在文件组上创建表/索引相结合,现在可以将所有销售人员放在文件组销售和采购文件组上。
IE - Spreading the I/O load.
IE - 传播I / O负载。
In short, I think schemas are an unknown and little used feature.
简而言之,我认为模式是一个未知的,很少使用的功能。
Check out my blog article on this fact.
看看我关于这个事实的博客文章。
#2
1
I assume that you are talking about SQL Server. You can join and reference between tables in different schemas. I see it mostly used for visual organization and/or for managing objects' permission (you can assign permissions at the schema-level).
我假设你在谈论SQL Server。您可以在不同模式中的表之间进行连接和引用。我发现它主要用于可视化组织和/或管理对象的权限(您可以在模式级别分配权限)。
If you are worried about any negative effects of doing dbo.table vs custom.table - there aren't any that I imagine you would encounter.
如果你担心做dbo.table与custom.table的任何负面影响 - 我想不到你会遇到任何负面影响。
#3
1
Schemas are just collections of database objects. They are useful for maintaining separation of sets of objects.
模式只是数据库对象的集合。它们对于保持对象集的分离很有用。
There is always at least one schema. For SQL Server it is named dbo.
始终至少有一个架构。对于SQL Server,它名为dbo。
One implication of having multiple schemas is that you will have to manage permissions for the various schemas. This is usually done via a role that's associated with the schema.
拥有多个模式的一个含义是您必须管理各种模式的权限。这通常通过与架构关联的角色完成。
Objects in one schema are available to objects from another, and there is no performance penalty in writing queries that use objects from multiple schemas.
一个模式中的对象可用于另一个模式中的对象,并且在编写使用来自多个模式的对象的查询时不会有性能损失。