因此,我试图设计一个数据库,使我能够连接一个产品与多个类别.这部分我已经猜到了.但我无法解决的是持有不同类型产品细节的问题.

例如,产品可以是一本书(在这种情况下,我需要引用该书的元数据,如ISBN、作者等),也可以是企业列表(具有不同的元数据).

我应该怎么用撞击呢?

推荐答案

这就是所谓的观察模式.

enter image description here

三个对象,例如

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

EDIT:

Jefferey raised a valid point (see comment), so I'll expand the answer.

The model allows for dynamic (on-fly) creation of any number of entites with any type of properties without schema changes. Hovewer, this flexibility has a price -- storing and searching is slower and more complex than in a usual table design.

Time for an example, but first, to make things easier, I'll flatten the model into a view.

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
;

引用 comments 中Jefferey的例子

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 ;

This looks complicated to write, but on a closer inspection you may notice a pattern in CTEs.

Now suppose we have a standard fixed schema design where each object property has its own column. The query would look something like:

select EntityID, Title, ISBN
from vModel
WHERE ObjectType       = 'book'
  and PublisherCountry = 'US'
  and YearPublished    = 2008
  and IsDiscontinued   = 'no'
  and Price            < 50
  and Currency         = 'USD'
;

Database相关问答推荐

如何使一个IP允许在运行GitHub操作到RDS数据库时,您不允许从外部访问数据库?

Prisma - 将属性的类型设置为枚举数组

这到底是做什么的 Class.forName("com.mysql.jdbc.Driver").newInstance();

如果我使用存储过程,我对 SQL 注入免疫吗?

如何将 MySQL 5.5.40 升级到 MySQL 5.7

类似 Hibernate 的 C++ 框架

在迁移中添加行

如何在数据库中存储具有动态数量属性的数据

ORM vs 传统数据库查询,它们的字段是什么?

C# 连接到数据库并列出数据库

The method setListAdapter(ArrayAdapter) is undefined for the type create

为什么有人需要内存数据库?

在服务器上排序还是在客户端排序?

如何更改 MySQL DB 中所有表的前缀?

无法加载时区?

如何将mysql的默认端口从3306更改为3360

表模块与域模型

如何在我的 SQL Server 代理作业(job)中创建一个将运行我的 SSIS 包的步骤?

使用 PHP/PDO 判断数据库表是否存在

数据库模式与数据库表空间?