oracle日志表设计,数据库设计 – 数据库日志表结构

因为只有几个不同的属性,所以我会远离名称 – 值对,并为每个属性提供一个具有正确名称和数据类型的单独表.我使用了通用Property_,仅用于演示.

这里的事情是确保在缺少属性表时不插入值,换句话说,所有属性值都是NOT NULL.

为了让生活更轻松,请定义视图

create view dbo.vLogs AS

select

LogCategoryName

, LogTime

, p1_Value

, p2_Value

, p3_Value

, p4_Value

, p5_Value

from LogEntry as e

left join Property_1 as p1 on p1.LogEntryId = e.LogEntryId

left join Property_2 as p2 on p2.LogEntryId = e.LogEntryId

left join Property_3 as p3 on p3.LogEntryId = e.LogEntryId

left join Property_4 as p4 on p4.LogEntryId = e.LogEntryId

left join Property_5 as p5 on p5.LogEntryId = e.LogEntryId

left join LogEntryCategory as x on x.LogEntryId = e.LogEntryId

left join LogCategory as c on c.LogCategoryID = x.LogCategoryID

这个视图(查询)看起来复杂而漫长;但是,如果您尝试下面的查询并查看执行计划,您可能会注意到选择列表中未提及的属性表未包含在计划中(未触及).<