[FOLIO-2031] Remove tenant name and mod_ prefix from PostgreSQL schema names, separate tenant data into diffent databases Created: 20/May/19  Updated: 17/Jul/20

Status: Open
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: New Feature Priority: P3
Reporter: Jon Miller Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: platform-backlog
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Relates
relates to FOLIO-1794 SPIKE: db connection provision and po... Open
relates to FOLIO-1935 Service creating ROLE and SCHEMA on t... Draft
Sprint:
Development Team: Core: Platform

 Description   

Please separate tenant data into separate databases. Currently, it appears that all tenant data is in a single database. The tenant the data belongs to is indicated by a prefix on all of the schemas. For example, our test environment currently has the "diku_" default prefix. Please remove that prefix along with the "mod_". All of the schema names are prefixed with "mod_". So, it is just redundant and requires more typing. Also, several of the schemas end with "_storage". It would be nice to eliminate that as well since it is obvious that what is in the database is storage.

diku_mod_audit
diku_mod_calendar
diku_mod_circulation_storage
diku_mod_configuration
diku_mod_data_import
diku_mod_event_config
diku_mod_feesfines
diku_mod_finance_storage
diku_mod_inventory_storage
diku_mod_kb_ebsco_java
diku_mod_login
diku_mod_notes
diku_mod_notify
diku_mod_orders_storage
diku_mod_password_validator
diku_mod_permissions
diku_mod_sender
diku_mod_source_record_manager
diku_mod_source_record_storage
diku_mod_tags
diku_mod_template_engine
diku_mod_users
diku_mod_vendors

The tenant name prefixes are the biggest issue as it makes it difficult to write code generically that will work in any environment. It would be much better to simply store tenant data is separate databases. This way all you need to do is vary the database connection string when connecting to the database using something like JDBC or the equivalent in other languages.

Using separate databases will also improve security and make database backups easier. Having multiple tenant's data all in one database is a very bad idea IMHO.



 Comments   
Comment by Jakub Skoczen [ 21/May/19 ]

Jon Miller you can save additonal typing by using:

SET search_path TO {tenant}_{module}
Comment by Jon Miller [ 21/May/19 ]

If you are doing ad hoc queries, that is useful. We are already using that. One of the issues is if you are using general-purpose code generation tools that aren't specific to PostgreSQL. For example, say you use a reverse engineering tool to generate matching Java class files for entities for the tables. The code generator is going to include the schema names in all the entity class mappings. Also, there are cases where the same table name appears in more than one module and would still need to be fully qualified. For example, account appears in diku_mod_feesfines and diku_mod_vendors. Another example is fund_distribution which appears in diku_mod_finance_storage and diku_mod_orders_storage.

There is also the separate issue of, do institutions really want their data stored together with other institution's data? Personally, I wouldn't. I would want my data in a separate secured database that only I have access to. This way you can easily back your database up also with only your data.

Comment by Ann-Marie Breaux (Inactive) [ 13/Jul/20 ]

Hi Jakub Skoczen I was at the migration subgroup meeting, and we're trying to clean up some Jiras. This is assigned to Core-Platform. Any updates on it? cc: Jon Miller Dale Arntson

Generated at Thu Feb 08 23:17:41 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.