1

I know schemas organize data within a database. I know a single database my contain multiple schemas. I know that for example a Postgres DB must have at least one schema.

What I would like to understand why were RDMS designed like that? Why would anyone bother to create schemas if they could just organize the data in different databases on the same database server?

Are there any benefits of having one database with multiple schemas vs multiple databases each with only one schema?

Jasper Citi
  • 153
  • 1
  • 5
  • THe short answer is: self defining data. By storing data definitions in a schema, it enables a person to "understand" the data in an unkown database. This understanding is limited to understanding the formal structure of the data. Comments can extend this self defining feature into the semantics of the data. System software can also read the schema and automate the adjustment of certain processes to conform to this structure. This includes interactive SQL and compilers, for starters. – Walter Mitty Nov 10 '21 at 11:35

1 Answers1

4

Schemas allow flexibility to the developer on how they want to organize their data, which can be utilized in a number of ways:

  1. Versioning - Some people organize structural versions of their entities across different versioned schemas. (Though I personally think this is probably not the most efficient way for versioning.)

  2. Categorizing Related Entities - This allows entities that all belong to the same database but differ in subject matter to be more organized. For example, an ERP system may organize its Product Sales related entities into a Sales schema but keep its Manufacturing related entities in a Manufacturing schema. This is especially a useful case when you're deploying software to multiple clients, and can use a single database as your unit of deployment.

  3. Security - Useful in conjunction with my second point, when you organize your data by schemas related to their subject matter then you get better control on securing those entities. Database security is not granular enough, and managing security at an individual entity level (for every entity) is usually too much work for what it's worth. Schemas help by being the root of a set of entities that likely will follow the same security rules and patterns.

J.D.
  • 37,483
  • 8
  • 54
  • 121