CODEWITHSUNDEEP
databasedatabase-designdata-structures
yodabar
yodabar

Reputation: 4769

Structuring database configuration settings

I am structuring the config settings of a database, abstracting as far as possible the config data.
In the most common LAMP environments, connecting to a database requires the following four information:

I would like to understand two things:

1 - Are credentials related to host or db?

If user and password are related to the host, they could access all the databases available on that host, so I could represent the config data this way (no matter it is an array, an object or other):

db_config
    └─db_host
        ├─db_user
        ├─db_password
        └─databases
            ├─db_name
            └─db_engine

If user and password are instead related to a specific database, they could access the only db_name specified; in this case I could schematize this way the config data:

db_config
    └─db_host
        └─databases
            └─db_name
                ├─db_engine
                ├─db_user
                └─db_password

2 - May databases with a different engine live on the same host?

Databases may be powered by different engines (e.g. PostgreSQL, MySQL, MSSQL, Oracle, Firebird, etc).
If credentials are related to the host, and if database with different engines may cohexist on the same host, the config data could be represented this way:

db_config
    └─db_host
        ├─db_user
        ├─db_password
        └─databases
            └─db_name
                └─db_engine

But this structure does not allow databases with different engines to have the same name.
Can databases with different engines cohexist on the same host and use the same name?
If yes, the config data could be structured so:

db_config
    └─db_host
        └─db_engine
            └─databases
                ├─db_name
                ├─db_user
                └─db_password

What data structure fits most possible real situations?

Upvotes: 0

Views: 72

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48246

It's kinda complicated, as different vendors have different features.

First, User/Password credentials should stand on their own. They might apply to a particular database server, but could be used for multiple databases too, or some other software. They are basically orthogonal.

Some databases support Active Directory/LDAP/Kerberos, SSL Certificates, PAM etc for login, not just username/passwords.

Some other points:

  • A database server might live across multiple hosts due to replication
  • A single computer can host multiple database servers on the same IP address (via different ports), or on different IP addresses. See PostgreSQL clusters
  • A database cluster can have multiple instances. See SQL Server instances
  • A database cluster can have many databases
  • A database can have many schemas
  • MySQL calls their schemas "databases"

In PostgreSQL at least, the users are children of the cluster, and not of a particular database, except I recall an obscure setting that lets you change this...

Upvotes: 1

Related Questions