Relationship between schema, database, User, table in databases [goto]

Beginners in the database tend to be confused about relational database schemas, databases, tables (table), users (user), andalways feel that they are inextricably linked. But they do not know where their connections and differences, and some problems often can not say why. Below, we discuss the relationship between its schema (schema), Database, table (tables), users (user) at the core of SQL Server.

First, let's figure out what a pattern is.

It is clear that the concept of schema inSQL Serveris only proposed in the 2005 version, so SQL Server2000 does not support the concept of schema (I have eaten here).

Schemas are also known as schemas, which are defined as collections of database entities that form a single namespace . A namespace is a collection in which the name of each element is unique. Here, we can look at the schema as a container for the objects in the database.

The above text description is too obscure, for a simple example, usually in the computer hard disk storage, we do not have all the contents of a folder, but will be different files according to a certain standard categories, put into different folders. In the database, the role of this is the architecture, database objects (tables, views, stored procedures, triggers, etc.) according to a certain standard, stored in different architectures. Those of you who have experienced Java programming know that namespace names are actually folder names, so we are very clear: An object can belong to only one schema , just as a file can be stored in a single folder. Unlike folders, schemas cannot be nested, that's all. As a result, the benefits of architecture are obvious-easy to manage.

Now, let's look at the relationship between the user and the schema (schema, or architecture).

From the above analysis, we know that a schema can accommodate multiple database objects, but not all users can access the content of a schema, which is called permissions. Take a look at the following table:

user1

y

n

TD valign= "Top" width= "> TD valign= "Top" width= "> TD valign= "Top" width= ">

	user2	user3	user4
schema1	y	n	n
schema2	y	n	y
Schema3	Y	N	Y	N

From this table, we can see that user 1 can access schema 1 and Schema 3, user 2 can access schema 1 and Schema 2, and so on.

in the SQL server2000 , the user and schema are not separated , and are separated by 2005. in fact, the user and architecture concept in 2000 is to assign a fixed pattern to the user, which is the following table:

	User1	User2	User3
Schema1	Y	—	—
Schema2	—	Y	—
Schema3	—	—	Y

As described above, the relationship between the user and the framework is many-to-many-one architecture can correspond to multiple users, and one user can correspond to multiple schemas.

Now, let's talk about the databaseand schema (schema).

To give a very simple example, we can think of the database as a large warehouse, the warehouse has a lot of room, theschema is the room, a schema represents a room, so in different rooms, we can put different things-some put food, some put clothes ...... And these are different things that correspond to the objects in our database.

Therefore, we can see that the database is a one-to-many relationship with schemas.

Summing up, in fact, our database is a large warehouse of data, and there are many many patterns created, respectively, with different database objects (including tables), and different modes have different permissions, so, different users have different access rights to access a schema database objects.

Relationship between schema, database, User, table in databases [goto]