I would like to create a generic table for managing entity types in order to simplify the standard maintenance tasks like activating and deactivating them.
create table entity_types (
entity_type_id number(14) not null,
entity_type varchar2(30) not null,
entity_type_group varchar2(30) not null,
activation_date date not null,
deactivation_date date,
--
constraint entity_types_pk primary key (entity_type_id),
constraint entity_types_k01 unique (entity_type_id, entity_type_group), -- Does not enforce uniqueness, just allows foreign keys to reference and ensures the entity type is appropriate for the group.
constraint entity_types_u01 unique (entity_type, deactivation_date)
);
I would also like to be able to reference the appropriate entity types by group by specifying the entity type group directly in the foreign key definition as a literal.
create table some_entities (
some_entity_id number(14) not null,
entity_type_id number(14) not null,
...
--
constraint some_entity_pk primary key (some_entity_id),
constraint some_entity_f01 foreign key (entity_type_id, 'some_group')
references entity_types (entity_type_id, entity_type_group)
);
I can accomplish the same effect by using virtual columns, but that clutters the table - especially if there are a number of different types being used on the same entity.