Defining Tenant Schema For Consortia
| Author | Adesh Singh |
|---|---|
| JIRA task | https://folio-org.atlassian.net/browse/MODFISTO-394 Define DB schema for storing tenants and create table https://folio-org.atlassian.net/browse/MODFISTO-400 |
| Business Requirements | Define DB schema for storing tenants and create table Define DB schema for storing user and affiliation associations and create table |
| Architects Review | Pending |
| PO review | Pending |
Revision History
Version | Date | Owner | Description | Reason |
|---|---|---|---|---|
| v1.0 | 02.28.2023 | Adesh Singh | Initial version | Add DB schema |
Overview
Problems
Todo
In scope
Todo
Out Of Scope
Todo
Architecturally Significant Requirements
Solution Design
Todo
Data Model
The "tenants" for consortia will be stored in the mod-consortia module; accordingly, the data schemas and tables described in this document are also for mod-consortia.
There will be 1 table for tenants named tenant. This table will be placed in the mod_consortia schema.
Liquibase Script
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
<changeSet id="MODFISTO-394@@create-tenant-table" author="singhadesh">
<createTable tableName="tenant">
<column name="id" type="text">
<constraints primaryKey="true" primaryKeyName="pk_tenant_id"/>
</column>
<column name="name" type="text">
<constraints unique="true" nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
tenant
Data Model and Data example for this table:
| Column | Column Datatype | Sense | is Mendatory? | is Unique? | Column Data Example |
|---|---|---|---|---|---|
| id | String | Y | Y | f0012346541234 | |
| name | String | Y | Y | Three Rivers Collage |
Data Model
The "user_tenat" is to storing users and affiliations associations
Liquibase Script
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
<changeSet id="MODFISTO-400@@create-user-tenant-table" author="azizbekxm">
<createTable tableName="user_tenant">
<column name="id" type="uuid">
<constraints primaryKey="true" primaryKeyName="pk_id"/>
</column>
<column name="user_id" type="uuid">
<constraints unique="false" nullable="false"/>
</column>
<column name="username" type="text">
<constraints unique="false" nullable="false"/>
</column>
<column name="tenant_id" type="text">
<constraints unique="false" nullable="false"
foreignKeyName="fk_tenant_id" referencedTableName="tenant" referencedColumnNames="id" />
</column>
<column name="is_primary" type="boolean" defaultValueBoolean="false">
<constraints unique="false" nullable="false"/>
</column>
</createTable>
<createIndex indexName="user_id_idx" tableName="user_tenant">
<column name="user_id"/>
</createIndex>
<createIndex indexName="user_name_idx" tableName="user_tenant">
<column name="username"/>
</createIndex>
</changeSet>
</databaseChangeLog>
tenant_user
Data Model and Data example for this table:
| Column | Column Data Type | Sense | Is mandatory? | Is unique? | Notes |
|---|---|---|---|---|---|
| id | UUID | How to identify a user and tenant table | Y | Y | UUID of user and tenant table and it is primary key |
| user_id | UUID | How to identify a user? | Y | Y | UUID of the user |
| username | String(text) | How to identify a username? | Y | N | username of the user |
| tenant_id | String(text) | What tenantId does the user associate with? | Y | N | UUID of the affiliation is associated with the user. - Foreign Key to tenant table |
| is_primary | Boolean | is Tenant primary for the user? | Y | N | Whether this tenant is the primary tenant for the user. |
