CODEWITHSUNDEEP
database
Kiddo
Kiddo

Reputation: 1175

Multi App One Database

I'm working on database design that concern multiple applications.

For example:

  1. system information for university (students, grade, subjects, major, etc)
  2. system information for library (members, books, category, etc)

the students in university and members in library have the same data.

So, to get synchronized data, I designed both applications' database into one database, which means the tables from both applications mixed up.

Is there a better solution for this?

Upvotes: 0

Views: 71

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95552

Most modern SQL dbms support schemas. A schema is essentially a container that you can use for any purpose. An obvious purpose is as a container for tables, views, stored procedures, etc., that functionally are closely related.

PostgreSQL's CREATE SCHEMA syntax

So you could create separate schemas for each of these things.

  • library
  • administration
  • extra-curricular activities
  • health and wellness
  • sports

Since each schema is a database object, you can usually GRANT and REVOKE privileges at the schema level. That makes it easier to administer schemas that have personally identifiable information in them, especially something like "health and wellness". (In fact, for health data, you might need even stronger isolation.)

The number of applications doesn't necessarily have anything to do with the number of schemas. I've worked on an operational database that had quite a few schemas, but had many hundreds of applications, programmed in at least 25 languages over three decades.

Upvotes: 1

Related Questions