Last updated: Dec 13, 2024
Check the Postgres, Db2 statements for the tables that are related to categories.
| Subject area | Table name | Description |
|---|---|---|
| Categories | categories | The governance categories that are defined. |
| Categories | category_collaborators | A list of all collaborators of a category. |
| Categories | category_tags | The tags that are associated with a category. |
| Categories | category_associations | The relationships between categories. |
categories table
This table contains information about the governance categories that are defined.
This table has the following columns:
category_id- The identifier of the category.name- The name of the category.description- The description of the category.created_by- The identifier of the user that created the category.created_on- The timestamp when the category was created.modified_by- The identifier of the user that last modified the category.modified_on- The timestamp when the category was last modified.
Postgres
CREATE TABLE statement:
create table categories(category_id varchar(128) not null,
name varchar(256) not null,
description text,
created_by varchar(128) not null,
created_on timestamp(6) not null,
modified_by varchar(128),
modified_on timestamp(6),
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(category_id));
Db2
CREATE TABLE statement:
create table categories(category_id varchar(128) not null,
name varchar(256) not null,
description clob,
created_by varchar(128) not null,
created_on timestamp(12) not null,
modified_by varchar(128),
modified_on timestamp(12),
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(category_id),
period SYSTEM_TIME (tech_start,
tech_end) )
category_collaborators table
This table contains a list of all collaborators of a category.
This table has the following columns:
category_id- The identifier of the category.user_id- The identifier of the user.role- The roles that are assigned to this user or user group, for example Owner, Admin, Editor, Reviewer, or Viewer.
Postgres
CREATE TABLE statement:
create table category_collaborators(category_id varchar(128) not null,
user_id varchar(128) not null,
role varchar(128) not null,
user_type varchar(16) not null,
tech_start TIMESTAMP(12) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(12) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(12) not null default CURRENT_TIMESTAMP,
primary key(category_id,
user_id,
role))
ALTER TABLE statement:
alter table category_collaborators add constraint fk_category_collaborators_categories_20 foreign key (category_id) references categories(category_id) on
delete
cascade on
update
no action
```</md-block>
</details>
<hr>
<hr id="5">
<details>
<summary>Db2</summary>
<md-block>
CREATE TABLE statement:
```sql {: .codeblock}
create table category_collaborators(category_id varchar(128) not null,
user_id varchar(128) not null,
role varchar(128) not null,
user_type varchar(16) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(category_id,
user_id,
role),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row
ALTER TABLE statement:
alter table category_collaborators add constraint fk_category_collaborators_categories_20 foreign key (category_id) references categories(category_id) on
delete
cascade on
update
no action
category_tags table
This table contains information about the tags that are associated with a category.
This table has the following columns:
tag_name- The name of the associated tag.category_id- The identifier of the category.
Postgres
CREATE TABLE statement:
create table category_tags(tag_name varchar(256) not null,
category_id varchar(128) not null,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(tag_name,
category_id));
ALTER TABLE statements:
alter table category_tags add constraint fk_category_tags_tags_27 foreign key (tag_name) references tags(tag_name) on
delete
cascade on
update
no action
alter table category_tags add constraint fk_category_tags_categories_25 foreign key (category_id) references categories(category_id) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
create table category_tags(tag_name varchar(256) not null,
category_id varchar(128) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(tag_name,
category_id),
period SYSTEM_TIME (tech_start,
tech_end) )
ALTER TABLE statements:
alter table category_tags add constraint fk_category_tags_tags_27 foreign key (tag_name) references tags(tag_name) on
delete
cascade on
update
no action
alter table category_tags add constraint fk_category_tags_categories_25 foreign key (category_id) references categories(category_id) on
delete
cascade on
update
no action
category_associations table
This table contains information about the relationships between categories.
This table has the following columns:
end1_category_id- The identifier of the source category.end2_category_id- The identifier of the target category.relationship_type- The relationship type between the two categories, for example parent_category.
Postgres
CREATE TABLE statement:
create table category_associations(end1_category_id varchar(128) not null,
end2_category_id varchar(128) not null,
relationship_type varchar(256) not null,
cr_definition_id varchar(128),
reverse_relationship_type varchar(256),
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(end1_category_id,
end2_category_id,
relationship_type))
ALTER TABLE statement:
alter table category_associations add constraint fk_category_associations_glossary_custom_relationship_def_7 foreign key (cr_definition_id) references glossary_custom_relationship_def(cr_definition_id) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
create table category_associations(end1_category_id varchar(128) not null,
end2_category_id varchar(128) not null,
relationship_type varchar(256) not null,
cr_definition_id varchar(128),
reverse_relationship_type varchar(256),
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(end1_category_id,
end2_category_id,
relationship_type),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row
ALTER TABLE statement:
alter table category_associations add constraint fk_category_associations_glossary_custom_relationship_def_7 foreign key (cr_definition_id) references glossary_custom_relationship_def(cr_definition_id) on
delete
cascade on
update
no action
Learn more
Parent topic: Reporting tables