在 SSMS 中组织数据库和表
这是我四五天前提出的问题的转发，零回复。希望这次能有更多好运...
（使用 SQL Server 2008）
在接下来的几周内，我计划将 SQL Server 引入急需合适数据服务器的办公室。目前，内部网络上严重依赖松散的 Excel 和 Access 文件（辅以大量令人难以理解的 VB 代码来进行数据操作）。
我们需要 SQL Server 来完成两件事：
1. 对于预先设计并持续捕获数据的内部数据库
2. 对于从客户处收到的数据集的临时上传，然后我们对其进行分析，
我是这个办公室中唯一熟悉 SQL 的人。我必须培训其他 5 或 6 个人使用它。
现在，我的问题是：你们如何设置数据库，以便可以轻松地使用 Management Studio 直观地识别存储内容的位置？更准确地说：如果这是一个 Windows 文件系统，它将看起来像这样：
c:\client work\client 1\piece of work 1 (db with 10 table)\
c:\client work\client 1\work 2 (带有 8 个表的数据库)\
c:\client work\client 1\workpiece 3 (有 7 个表的数据库)\
c:\internal\accounting system\some db 有 8 个表\
c:\internal\accounting system\一些带有 5 个表的数据库\
c：\内部\其他一些系统\一些带有7个表的数据库\
等。
简单地说，我需要按内部和客户工作进行可视化划分。我需要将客户工作分配给不同的客户。对于每个客户，我需要分解不同的不同工作组。 （内部工作遵循类似的模式）。
我所知道的解决方案：
- 运行多个数据服务器（例如一台内部服务器，一台用于客户端工作）。虽然不确定这有什么缺点，但
- 将架构分配给表
我很想听听您的建议！
This is a repost of a question I asked 4 or 5 days ago, with zero response. Hoping for more luck this time...
(Using SQL Server 2008)
Within the next few weeks I plan to introduce SQL server to an office that is in dire need of a proper data server. Currently there is a heavy reliance on loose Excel and Access file (supplemented with frighteningly large amount of impenetrable VB code to do data manipulations) strewn all over the internal network.
We need SQL server for two things:
1. For internal databases that will be designed upfront and will be capturing data on an ongoing basis
2. For ad hoc uploads of datasets received from clients, which we then analyse
I am the only person in this office who is familiar with SQL. I will have to train the other 5 or 6 people to use it.
Now, my question is this: how would you guys set up the DBs so that it would be easy using Management Studio to visually recognize where what is being stored? To be more precise: if this were a windows file system it would look something like this:
c:\client work\client 1\piece of work 1 (db with 10 tables)\
c:\client work\client 1\piece of work 2 (db with 8 tables)\
c:\client work\client 1\piece of work 3 (db with 7 tables)\
c:\internal\accounting system\some db with 8 tables\
c:\internal\accounting system\some db with 5 tables\
c:\internal\some other system\some db with 7 tables\
etc.
So briefly, I need to visually split by internal and client work. Client work I need to split by different clients. For each client I need to split out the different distinct sets of work. (Internal work follows a similar pattern).
Solutions that I am aware of:
- Run multiple data servers (e.g. one internal, one for client work). Not sure what the cons of this would be though
- Assign schemas to tables
I would love to hear your suggestions!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
用于管理 SQL Server 的组织工具是实例、数据库和架构：
一台服务器可以运行多个实例。实例基本上是同一台计算机上完全独立的服务器实例。
一个实例可以管理多个数据库。数据库是完整性的标准边界 - 您（通常）备份整个数据库，引用完整性仅限于同一数据库中的对象之间，等等。
每个数据库可以包含多个模式，这允许您组织代码。
所有这些“容器”都以某种方式与安全相关。
我建议您首先进行组织数据和流程清单，以便了解您正在处理哪些数据、谁使用这些数据以及如何使用这些数据 - 特别注意公共或协作的数据（由某些人一起使用的数据）以及哪些数据需要划分访问权限（仅由特定角色使用）。 SQL Server 实际上并不是存储非结构化数据的好地方 - 例如，我不会将其视为文件服务器的简单替代品。
从这里开始，继续为您的用户定义角色。拥有角色是比向单个用户分配权限更好的策略。它记录了访问的语义（任何执行此角色的人都需要此访问权限，而不是用户的身份 - john 和 kate 需要访问权限 - 这不会告诉您他们为什么需要访问权限）。确保角色足够细粒度。像 AccountsReceivable 这样的部门角色几乎不如 PaymentApprover 或 InvoiceProcessor 或 AccountsSupervisor 有用。用户可以扮演多个角色 - 这将为您的基础设施提供更多的自我记录能力，并减少安全漏洞和麻烦。
这应该有助于定义您需要哪些容器以及授予哪些访问权限并从那里指导您的数据基础设施。
至于让用户直接访问，我同意 Randy Minder 的观点，SQL Server 充其量只是一个专家用户工具。如果他们熟悉 Access，一个好的选择是让他们针对 SQL Server 中精心设计和选择的视图使用 Access，直到他们准备好采用更系统的数据工程方法。
Your organizational tools for managing SQL Server are instances, databases and schemas:
A server can run multiple instances. An instance is basically a completely separate server instance on the same machine.
An instance can manage multiple databases. The database is the standard boundary of integrity - you (usually) back up an entire database, referential integrity is constrained to being between objects in the same database, etc.
Each database can contain multiple schemas, which allow you to organize code.
All these "containers" relate to security in some way.
I recommend that you take an organization data and process inventory first, so that you understand what data you are dealing with, who uses it and how - with special attention on data which is public or collaborative (data used by certain people together) and which needs to be compartmentalized access (only used by a particular role). SQL Server is not really a great place of choice to be storing unstructured data - I would not view it as a simple replacement of a file server, for instance.
From there, proceed to define roles for your users. Having roles is a lot better strategy than assigning rights to individual users. It documents the semantic meaning of the access (any person performing this role needs this access as opposed to the user's identity - john and kate need access - this tells you nothing about why they need access). Be certain that the roles are sufficiently fine-grained. A departmental role like AccountsReceivable isn't nearly as useful as PaymentApprover or InvoiceProcessor or AccountsSupervisor. Users can act in multiple roles - this will give you a lot more self-documenting ability in your infrastructure and a lot fewer security holes and headaches.
This should help to define which containers you will need and what access to grant and guide your data infrastructure from there.
As far as giving users direct access, I'm with Randy Minder, SQL Server is only an expert user tool at best. If they are familiar with Access, a good option is to let them use Access against carefully designed and chosen views in SQL Server until they are ready for a more systematic data engineering approach.
IMO，数据库的用户不必知道或关心数据库的设置位置或方式。除非他们接受过良好的 SQL 培训，否则不应授予他们访问 SSMS 的权限。这是一场即将发生的灾难。您应该创建允许用户访问他们需要的数据的应用程序和/或报告。这样他们就不会关心数据位于哪里，也不需要知道。
IMO, users of your databases should not have to know or care where or how your databases are set up. And they shouldn't be given access to SSMS unless they are well trained in SQL. This is a disaster waiting to happen. You should be creating applications and/or reports that allow the user access to the data they need. That way they don't care where the data sits, and don't need to know.