[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: |
|
||||||||||||
| 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 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 |