设计数据库以保存不同的元数据信息

Question

因此，我正在尝试设计一个数据库，以使我可以将一个产品与多个类别联系起来。我已经知道了这一部分。但是我无法解决的问题是持有不同类型的产品详细信息。例如，产品可以是一本书（在这种情况下，我将需要引用该书的元数据，例如isbn，author等），也可以是业务清单（具有不同的元数据）。我该如何解决？

Answer 1

这称为观察模式。

在此处输入图片说明

以三个对象为例

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'

;

Answer 2

我本来不打算回答，但是现在被接受的答案是一个非常糟糕的主意。关系数据库绝对不能用于存储简单的属性-值对。这将在以后引起很多问题。

解决此问题的最佳方法是为每种类型创建一个单独的表。

Product

-------

ProductId

Description

Price

(other attributes common to all products)

Book

----

ProductId (foreign key to Product.ProductId)

ISBN

Author

(other attributes related to books)

Electronics

-----------

ProductId (foreign key to Product.ProductId)

BatteriesRequired

etc.

每个表的每一行都应代表一个关于真实世界的命题，并且表的结构及其约束应反映所代表的现实。您越接近这个理想，数据将越干净，并且以其他方式进行报告和扩展系统也就越容易。它还将更有效地运行。

Answer 3

您可以采用无模式方法：

将元数据作为JSON对象（或其他序列化形式）保存在TEXT列中，但由于稍后说明的原因，JSON更好。

该技术的优点：

1. 更少的查询：您只需一次查询即可获取所有信息，而无需“定向”查询（获取元元数据）和联接。

2. 您可以随时添加/删除所需的任何属性，而无需更改表（这在某些数据库中是有问题的，例如，Mysql锁定了表，而使用大型表则需要很长时间）

3. 由于它是JSON，因此您不需要在后端进行额外的处理。您的网页（我假设它是一个Web应用程序）仅从Web服务中读取JSON，仅此而已，您可以根据需要使用JSON对象和javascript。

问题：

1. 潜在的浪费空间是，如果您有100本书与同一位作者在一起，那么一个作者表（其中所有书籍都只有author_id）是更经济的空间选择。

2. 需要实现索引。由于您的元数据是JSON对象，因此您不会立即拥有索引。但是，为所需的特定元数据实现特定索引非常容易。例如，您想按作者进行索引，因此您可以使用author_id和item_id创建一个author_idx表，当有人搜索作者时，您可以查找此表和项目本身。

根据规模，这可能是一个过大的杀伤力。在较小规模的连接上可以正常工作。