Oracle 建模不良的表格

我收到了一个请求来建立一个看起来像这样的表:

create table META_TABLE ( 
    TABLE_ITEM int constraint META_TABLE_PK primary key not null,  
    ITEM_TYPE char(30) not null, -- service, retail, maintenance  
    CREATE_DATE date not null,  
    PARTY1_ID int not null,  
    PARTY2_ID int not null,  
    DESCRIPTION char(200) not null,  
    STATUS char(30) not null,  
    SVC_TYPE char(30) null,  
    SVC_HOURS number null, 
    MILEAGE number null,  
    PRODUCT_ID int null,  
    QUANTITY int null,  
    TOTAL_SALE number(10,4) null,  
    EQUIP_ID int null,  
    MAINT_TYPE char(30) null  
    );

请注意，列列表仅供说明; 我们从事医疗保健，我不想使用诸如ICD10，SNOMED，HCPCS等深奥的术语。应用程序如果主要是OLTP，而不是OLAP/BI。几列仅用于 “ITEM_TYPE” 的特定值。一组插入看起来像:

insert into META_TABLE  
select 1, 'SERVICE', sysdate, 11, 6001, 'Service example 1', 'SCHEDULED', 'HVAC', 3.5, 16, null, null, null, null, null from dual 
union 
select 2, 'SERVICE', sysdate, 37, 1203, 'Service example 2', 'COMPLETE', 'Pool', 2, 9, null, null, null, null, null from dual 
union 
select 3, 'RETAIL', sysdate, 16, 9600, 'Retail example 1', 'COMPLETE', null, null, null, 654321, 3, 87.52, null, null from dual 
union 
select 4, 'RETAIL', sysdate, 203, 1107, 'Retail example 2', 'COMPLETE', null, null, null, 2468, 11, 23.45, null, null from dual 
union 
select 5, 'MAINTENANCE', sysdate, 102, 0, 'Maintenance example 1', 'SCHEDULED', null, null, null, null, null, null, 16, 'PM' from dual 
union 
select 6, 'MAINTENANCE', sysdate, 102, 0, 'Maintenance2', 'IN PROGESS', null, null, null, null, null, null, 17, 'CLEANING' from dual ;

我更喜欢构建四个表，如下所示:

create table ITEM_TABLE ( 
    TABLE_ITEM int constraint ITEM_TABLE_PK primary key not null,  
    ITEM_TYPE char(30) not null,  
    CREATE_DATE date not null,  
    PARTY1_ID int not null,  
    PARTY2_ID int not null,  
    DESCRIPTION char(200) not null,  
    STATUS char(30) not null  
    );

create table ITEM_SERVICE_TABLE ( 
    SERVICE_TABLE_ITEM int constraint ITEM_SERVICE_TABLE_PK primary key not null,  
    TABLE_ITEM int not null,  
    SVC_TYPE char(30) not null,  
    SVC_HOURS number not null, 
    MILEAGE number not null,  
    constraint TABLE_ITEM_FK1 foreign key (TABLE_ITEM) references ITEM_TABLE (TABLE_ITEM)  
    );

create table ITEM_RETAIL_TABLE ( 
    RETAIL_TABLE_ITEM int constraint ITEM_RETAIL_TABLE_PK primary key not null,  
    TABLE_ITEM int not null,  
    PRODUCT_ID int not null,  
    QUANTITY int not null,  
    TOTAL_SALE number(10,4) not null,  
    constraint TABLE_ITEM_FK2 foreign key (TABLE_ITEM) references ITEM_TABLE (TABLE_ITEM)  
    );

create table ITEM_MAINTENANCE_TABLE ( 
    MAINTENANCE_TABLE_ITEM int constraint ITEM_MAINTENANCE_TABLE_PK primary key not null,  
    TABLE_ITEM int not null,  
    EQUIP_ID int not null,  
    MAINT_TYPE char(30) not null,  
    constraint TABLE_ITEM_FK3 foreign key (TABLE_ITEM) references ITEM_TABLE (TABLE_ITEM)  
    );

我知道提出的模型不在2NF中 (因此不在3NF中)，我可以用抽象/学术术语来解释这一点，但我不确定如何在商业语言中阐明为什么这是次优的。你能给我一些弹药吗，或者你知道一个好的资源吗？

Thanx，
唐

嗯，因为meta_table允许null，可以说它甚至不在1NF!

我不确定您是否有2NF违规行为。你怎么知道这张表不在2NF里？候选密钥是什么？哪些非键列取决于候选键的列的子集？

无论如何，单表仍然有几个缺点。

Ensuring the correct columns are set

所以我猜ITEM_TYPE确定哪些其他列不为null。这意味着你需要复杂的检查约束，验证正确的约束是按照以下方式设置的:

case when item_type = 'SERVICE' then svc_type end is not null and 
  case when item_type = 'SERVICE' then svc_hours end is not null and
  case when item_type = 'SERVICE' then coalesce(product_id, quantity, total_sale, etc) end is null

这很容易出错。以及添加新项目或属性时要更新的faff。如果给定类型的任何属性都是可选的，那么这真是令人头疼。

Query results

您需要确保所有查询仅选择适当的类型及其关联列。这很容易被忽视。

对于子表，您可以仅从该表中进行选择，也可以加入父表。无论哪种情况，您都不太可能意外获取不正确的项目。

Referential integrity

是否有任何类型具有特定于其自身的子表？例如，服务项目是否具有只能是其子级而不是零售的行？如果是这样，您需要付出一些努力以确保FKs防止这种情况。这涉及具有唯一约束，包括类型并将该列复制到子级。使用单独的子表，您只需使FK正常。

尽管可以说您应该在item * 表上执行此操作，以确保它们仅存储正确父级的行。

Performance

与一个大表查询，做全面扫描，但只返回一个项目类型有更多 (不必要) 的数据处理。当然，这也可以走另一条路。如果您定期获取多个项目类型的行，则加入的开销可能会降低您的速度。最终，这是您需要在应用程序中进行测试的内容。

另外: 使用char是什么？你几乎肯定想使用varchar2，除非你喜欢处理空白填充字符串:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476