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

Question

因此，我正在尝试设计一个数据库，以使我可以将一个产品与多个类别联系起来。我已经知道了这一部分。但是我无法解决的问题是持有不同类型的产品详细信息。

例如，产品可以是一本书（在这种情况下，我将需要引用该书的元数据，例如isbn，author等），也可以是业务清单（具有不同的元数据）。

我该如何解决？

Answer 1

这称为观察模式。在此处输入图片说明以三个对象为例BookTitle = 'Gone with the Wind'&nbsp;Author = 'Margaret Mitchell'ISBN&nbsp; &nbsp;= '978-1416548898'CatName = 'Phoebe'Color = 'Gray'TailLength = 9 'inch'Beer BottleVolume = 500 'ml'Color = 'Green'这是表格的样子：EntityEntityID&nbsp; &nbsp; Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Description&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; 'Book'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'To read'&nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; 'Cat'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Fury cat'&nbsp;&nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; 'Beer Bottle'&nbsp; &nbsp; &nbsp;'To ship beer in'。PropertyTypePropertyTypeID&nbsp; &nbsp;Name&nbsp; &nbsp; &nbsp; &nbsp; IsTrait&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Description&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Height'&nbsp; &nbsp; &nbsp;'NO'&nbsp; &nbsp; &nbsp; &nbsp;'For anything that has height'&nbsp;&nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Width'&nbsp; &nbsp; &nbsp; 'NO'&nbsp; &nbsp; &nbsp; &nbsp;'For anything that has width'&nbsp;&nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Volume'&nbsp; &nbsp; &nbsp;'NO'&nbsp; &nbsp; &nbsp; &nbsp;'For things that can have volume'&nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Title'&nbsp; &nbsp; &nbsp; 'YES'&nbsp; &nbsp; &nbsp; 'Some stuff has title'&nbsp;&nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Author'&nbsp; &nbsp; &nbsp;'YES'&nbsp; &nbsp; &nbsp; 'Things can be authored'&nbsp;&nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Color'&nbsp; &nbsp; &nbsp; 'YES'&nbsp; &nbsp; &nbsp; 'Color of things'&nbsp;&nbsp; &nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'ISBN'&nbsp; &nbsp; &nbsp; &nbsp;'YES'&nbsp; &nbsp; &nbsp; 'Books would need this'&nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TailLength' 'NO'&nbsp; &nbsp; &nbsp; &nbsp;'For stuff that has long tails'&nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Name'&nbsp; &nbsp; &nbsp; &nbsp;'YES'&nbsp; &nbsp; &nbsp; 'Name of things'。PropertyPropertyID&nbsp; &nbsp;EntityID&nbsp; PropertyTypeID&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp;-- book, title&nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp;-- book, author&nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp;-- book, isbn&nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; &nbsp;-- cat, name&nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp;-- cat, color&nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp;-- cat, tail length&nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp;-- beer bottle, volume&nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp;-- beer bottle, color。MeasurementPropertyID&nbsp; &nbsp; &nbsp;Unit&nbsp; &nbsp; &nbsp; &nbsp;Value&nbsp;&nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'inch'&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- cat, tail length&nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'ml'&nbsp; &nbsp; &nbsp; &nbsp; 500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- beer bottle, volume。TraitPropertyID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Value&nbsp;&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Gone with the Wind'&nbsp; &nbsp; &nbsp;-- book, title&nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Margaret Mitchell'&nbsp; &nbsp; &nbsp; -- book, author&nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'978-1416548898'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- book, isbn&nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Phoebe'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- cat, name&nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Gray'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- cat, color&nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Green'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- beer bottle, color编辑：杰弗里提出了一个正确的观点（见评论），所以我将扩大答案。该模型允许动态（动态）创建具有任何类型的属性的任意数量的实体，而无需更改架构。但是，这种灵活性要付出代价-与通常的桌子设计相比，存储和搜索更慢，更复杂。是一个例子了，但是首先，为了使事情变得容易，我将模型展平为一个视图。create view vModel as&nbsp;select&nbsp;&nbsp; &nbsp; &nbsp; e.EntityId&nbsp; &nbsp; , x.Name&nbsp; as PropertyName&nbsp; &nbsp; , m.Value as MeasurementValue&nbsp; &nbsp; , m.Unit&nbsp; &nbsp; , t.Value as TraitValuefrom Entity&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;as ejoin Property&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;as p on p.EntityID&nbsp; &nbsp; &nbsp; &nbsp;= p.EntityIDjoin PropertyType&nbsp; &nbsp; &nbsp;as x on x.PropertyTypeId = p.PropertyTypeIdleft join Measurement as m on m.PropertyId&nbsp; &nbsp; &nbsp;= p.PropertyIdleft join Trait&nbsp; &nbsp; &nbsp; &nbsp;as t on t.PropertyId&nbsp; &nbsp; &nbsp;= p.PropertyId;从评论中使用杰弗里的例子with&nbsp;q_00 as ( -- all books&nbsp; &nbsp; select EntityID&nbsp; &nbsp; from vModel&nbsp; &nbsp; where PropertyName = 'object type'&nbsp; &nbsp; &nbsp; and TraitValue&nbsp; &nbsp;= 'book'&nbsp;),q_01 as ( -- all US books&nbsp; &nbsp; select EntityID&nbsp; &nbsp; from vModel as a&nbsp; &nbsp; join q_00&nbsp; &nbsp;as b on b.EntityID = a.EntityID&nbsp; &nbsp; where PropertyName = 'publisher country'&nbsp; &nbsp; &nbsp; and TraitValue&nbsp; &nbsp;= 'US'&nbsp;),q_02 as ( -- all US books published in 2008&nbsp; &nbsp; select EntityID&nbsp; &nbsp; from vModel as a&nbsp; &nbsp; join q_01&nbsp; &nbsp;as b on b.EntityID = a.EntityID&nbsp; &nbsp; where PropertyName&nbsp; &nbsp; &nbsp;= 'year published'&nbsp; &nbsp; &nbsp; and MeasurementValue = 2008&nbsp;),q_03 as ( -- all US books published in 2008 not discontinued&nbsp; &nbsp; select EntityID&nbsp; &nbsp; from vModel as a&nbsp; &nbsp; join q_02&nbsp; &nbsp;as b on b.EntityID = a.EntityID&nbsp; &nbsp; where PropertyName = 'is discontinued'&nbsp; &nbsp; &nbsp; and TraitValue&nbsp; &nbsp;= 'no'&nbsp;),q_04 as ( -- all US books published in 2008 not discontinued that cost less than $50&nbsp; &nbsp; select EntityID&nbsp; &nbsp; from vModel as a&nbsp; &nbsp; join q_03&nbsp; &nbsp;as b on b.EntityID = a.EntityID&nbsp; &nbsp; where PropertyName&nbsp; &nbsp; &nbsp;= 'price'&nbsp; &nbsp; &nbsp; and MeasurementValue < 50&nbsp;&nbsp; &nbsp; &nbsp; and MeasurementUnit&nbsp; = 'USD')select&nbsp; &nbsp; &nbsp; EntityID&nbsp; &nbsp; , max(case PropertyName when 'title' than TraitValue else null end) as Title&nbsp; &nbsp; , max(case PropertyName when 'ISBN'&nbsp; than TraitValue else null end) as ISBNfrom vModel as ajoin q_04&nbsp; &nbsp;as b on b.EntityID = a.EntityIDgroup by EntityID ;编写起来似乎很复杂，但是仔细检查后，您可能会注意到CTE中的模式。现在假设我们有一个标准的固定模式设计，其中每个对象属性都有自己的列。查询如下所示：select EntityID, Title, ISBNfrom vModelWHERE ObjectType&nbsp; &nbsp; &nbsp; &nbsp;= 'book'&nbsp; and PublisherCountry = 'US'&nbsp; and YearPublished&nbsp; &nbsp; = 2008&nbsp; and IsDiscontinued&nbsp; &nbsp;= 'no'&nbsp; and Price&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; < 50&nbsp; and Currency&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= 'USD';

Answer 2

我本来不打算回答，但是现在被接受的答案是一个非常糟糕的主意。关系数据库绝对不能用于存储简单的属性-值对。这将在以后引起很多问题。解决此问题的最佳方法是为每种类型创建一个单独的表。Product-------ProductIdDescriptionPrice(other attributes common to all products)Book----ProductId (foreign key to Product.ProductId)ISBNAuthor(other attributes related to books)Electronics-----------ProductId (foreign key to Product.ProductId)BatteriesRequiredetc.每个表的每一行都应代表一个关于真实世界的命题，并且表的结构及其约束应反映所代表的现实。您越接近这个理想，数据将越干净，并且以其他方式进行报告和扩展系统也就越容易。它还将更有效地运行。

Answer 3

您可以采用无模式方法：将元数据作为JSON对象（或其他序列化形式）保存在TEXT列中，但由于稍后说明的原因，JSON更好。该技术的优点：更少的查询：您只需一次查询即可获取所有信息，而无需“定向”查询（获取元元数据）和联接。您可以随时添加/删除所需的任何属性，而无需更改表（这在某些数据库中是有问题的，例如，Mysql锁定了表，而使用大型表则需要很长时间）由于它是JSON，因此您不需要在后端进行额外的处理。您的网页（我假设它是一个Web应用程序）仅从Web服务中读取JSON，仅此而已，您可以根据需要使用JSON对象和javascript。问题：潜在的浪费空间是，如果您有100本书与同一位作者在一起，那么一个作者表（其中所有书籍都只有author_id）是更经济的空间选择。需要实现索引。由于您的元数据是JSON对象，因此您不会立即拥有索引。但是，为所需的特定元数据实现特定索引非常容易。例如，您想按作者进行索引，因此您可以使用author_id和item_id创建一个author_idx表，当有人搜索作者时，您可以查找此表和项目本身。根据规模，这可能是一个过大的杀伤力。在较小规模的连接上可以正常工作。