CODEWITHSUNDEEP
postgresqldatabase-designdatabase-schema
IAmYourFaja
IAmYourFaja

Reputation: 56914

Decentralizing Database Structure

Although this question fancies PostgreSQL, it is still a general DB question.

I have always been curious about the term schema as it relates to databases. Recently, we switched over to using PostgreSQL, where that term has actual significance to the underlying database structure.

In PostgreSQL-land, the decentralized structure is as follows:

DB Server (`some-server.com:5432`)
    >> Database (`fizz`)
        >> Schema (`buzz`)
            >> Table (`foo`)

Thus, the FQDN for table [foo] is fizz.buzz.foo.

I understand that a "database" is a logical grouping of tables. For instance, an organization might have a "domain" database where all POJOs/VOs are persisted, an "orders" database where all sales-related info is stored, and a "logging" databases where all log messages get sent for future analysis, etc.

The introduction of this "schema" construct in between the database and its tables has me very confused, and the PostgreSQL documentation is a little too heavy-handed (and lacking good examples) for a newbie such as myself to understand.

I'm wondering if anyone can give me a laymen's description of not only what this "schema" construct is within the realm of PostgreSQL (and how it relates databases to tables), but I'm wondering what it means to database structures in general.

Thanks in advance!

Upvotes: 1

Views: 681

Answers (2)

Algy Taylor
Algy Taylor

Reputation: 834

Late to the party, but ..

I use schemas to split tables in to groups that are used by different applications that share a few tables, for example.

  • users
  • application1
  • application2

Here, if we log in with app1, we see users + application1; if we log in to app2, we see users and application2. So our user data can be shared between both, without exposing app1 users to app2 data. It also means that a superuser can do queries across both sets of data.

Upvotes: 0

Eric
Eric

Reputation: 95123

Think of schemas as namespaces. We can use them to logically group tables (such as a People schema). Additionally, we can assign security to that schema so we can allow certain folks to look at a Customer schema, but not an Employee schema. This allows us to have a granularity of control of security just above an object level but below the database level.

Security is probably the most important reason to use schemas, but I've seen them used for logical groupings as well. It just depends on what you need them for.

Upvotes: 2

Related Questions