Categories reporting tables

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

Was the topic helpful?

0/1000