mysql元数据与表数据不一致_设计数据库以保存不同的元数据信息

这称为观察模式。

在此处输入图片说明

以三个对象为例

Book

Title = 'Gone with the Wind'

Author = 'Margaret Mitchell'

ISBN   = '978-1416548898'

Cat

Name = 'Phoebe'

Color = 'Gray'

TailLength = 9 'inch'

Beer Bottle

Volume = 500 'ml'

Color = 'Green'

这是表格的样子：

Entity

EntityID    Name            Description

1        'Book'            'To read'

2        'Cat'             'Fury cat'

3        'Beer Bottle'     'To ship beer in'

。

PropertyType

PropertyTypeID   Name        IsTrait         Description

1            'Height'     'NO'       'For anything that has height'

2            'Width'      'NO'       'For anything that has width'

3            'Volume'     'NO'       'For things that can have volume'

4            'Title'      'YES'      'Some stuff has title'

5            'Author'     'YES'      'Things can be authored'

6            'Color'      'YES'      'Color of things'

7            'ISBN'       'YES'      'Books would need this'

8            'TailLength' 'NO'       'For stuff that has long tails'

9            'Name'       'YES'      'Name of things'

。

Property

PropertyID   EntityID  PropertyTypeID

1           1              4     -- book, title

2           1              5     -- book, author

3           1              7     -- book, isbn

4           2              9     -- cat, name

5           2              6     -- cat, color

6           2              8     -- cat, tail length

7           3              3     -- beer bottle, volume

8           3              6     -- beer bottle, color

。

Measurement

PropertyID     Unit       Value

6          'inch'       9          -- cat, tail length

7          'ml'        500         -- beer bottle, volume

。

Trait

PropertyID         Value

1         'Gone with the Wind'     -- book, title

2         'Margaret Mitchell'      -- book, author

3         '978-1416548898'         -- book, isbn

4         'Phoebe'                 -- cat, name

5         'Gray'                   -- cat, color

8         'Green'                  -- beer bottle, color

编辑：

杰弗里提出了一个正确的观点(见评论)，所以我将扩大答案。

该模型允许动态(动态)创建具有任何类型的属性的任意数量的实体，而无需更改架构。但是，这种灵活性要付出代价-与通常的桌子设计相比，存储和搜索更慢，更复杂。

是一个例子了，但是首先，为了使事情变得容易，我将模型展平为一个视图。

create view vModel as

select

e.EntityId

, x.Name  as PropertyName

, m.Value as MeasurementValue

, m.Unit

, t.Value as TraitValue

from Entity           as e

join Property         as p on p.EntityID       = p.EntityID

join PropertyType     as x on x.PropertyTypeId = p.PropertyTypeId

left join Measurement as m on m.PropertyId     = p.PropertyId

left join Trait       as t on t.PropertyId     = p.PropertyId

;

从评论中使用杰弗里的例子

with

q_00 as ( -- all books

select EntityID

from vModel

where PropertyName = 'object type'

and TraitValue   = 'book'

),

q_01 as ( -- all US books

select EntityID

from vModel as a

join q_00   as b on b.EntityID = a.EntityID

where PropertyName = 'publisher country'

and TraitValue   = 'US'

),

q_02 as ( -- all US books published in 2008

select EntityID

from vModel as a

join q_01   as b on b.EntityID = a.EntityID

where PropertyName     = 'year published'

and MeasurementValue = 2008

),

q_03 as ( -- all US books published in 2008 not discontinued

select EntityID

from vModel as a

join q_02   as b on b.EntityID = a.EntityID

where PropertyName = 'is discontinued'

and TraitValue   = 'no'

),

q_04 as ( -- all US books published in 2008 not discontinued that cost less than $50

select EntityID

from vModel as a

join q_03   as b on b.EntityID = a.EntityID

where PropertyName     = 'price'

and MeasurementValue < 50

and MeasurementUnit  = 'USD'

)

select

EntityID

, max(case PropertyName when 'title' than TraitValue else null end) as Title

, max(case PropertyName when 'ISBN'  than TraitValue else null end) as ISBN

from vModel as a

join q_04   as b on b.EntityID = a.EntityID

group by EntityID ;

编写起来似乎很复杂，但是仔细检查后，您可能会注意到CTE中的模式。

现在假设我们有一个标准的固定模式设计，其中每个对象属性都有自己的列。查询如下所示：

select EntityID, Title, ISBN

from vModel

WHERE ObjectType       = 'book'

and PublisherCountry = 'US'

and YearPublished    = 2008

and IsDiscontinued   = 'no'

and Price            < 50

and Currency         = 'USD'

;