database-design - tool - sqldbm




設計數據庫以保存不同的元數據信息 (4)

在這種問題中,您有三種選擇:

  1. 創建一個包含“通用”列的表。 例如,如果您同時銷售書籍和烤麵包機,則您的烤麵包機可能沒有ISBN和標題,但它們仍然具有某種產品標識符和描述。 因此,請為字段指定通用名稱,例如“product_id”和“description”,對於書籍,product_id是ISBN,對於烤麵包機,它是製造商的部件號等。

當真實世界的實體都以相同的方式處理時,這是有效的,至少在大多數情況下,並且因此,如果不是“相同的”數據,則必須具有至少類似的數據。 當存在真正的功能差異時,這會崩潰。 就像對於烤麵包機我們正在計算瓦特=伏特*安培,很可能沒有相應的書籍計算。 當你開始創建一個pages_volts字段,其中包含書籍的頁數和烤麵包機的電壓時,事情已經失去控制。

  1. 使用Damir建議的房產/價值計劃。 請參閱我在帖子中對其優缺點的評論。

  2. 我通常建議的是類型/子類型方案。 為“product”創建一個包含類型代碼和通用字段的表。 然後,對於每種真實類型 - 書籍,烤麵包機,貓,等等 - 創建一個連接到產品表的單獨表。 然後,當您需要執行特定於圖書的處理時,請處理書籍表。 當您需要進行通用處理時,請處理產品表。

所以我試圖設計一個數據庫,允許我將一個產品與多個類別連接起來。 這部分我想通了。 但我無法解決的問題是持有不同類型的產品細節。

例如,產品可能是一本書(在這種情況下,我需要像isbn,作者等那樣引用該書的元數據),或者它可能是商業列表(具有不同的元數據)。

我應該怎麼解決這個問題?


您可以使用無模式方法:

將元數據作為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表,當有人搜索作者時,您可以查找此表和項目本身。

根據比例,這可能是一種矯枉過正。 在較小規模的連接上工作得很好。


該產品應該打字。 例如,在產品表中包含type_id,它指向您將支持的產品類別,並讓您知道要查詢哪些其他表以獲取相應的相關屬性。


這稱為觀察模式。

三個對象,例如

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

編輯:

Jefferey提出了一個有效的觀點(見評論),所以我會擴大答案。

該模型允許動態(動態)創建具有任何類型屬性的任意數量的entites而無需更改架構。 Hovewer,這種靈活性具有價格 - 存儲和搜索比通常的表格設計更慢,更複雜。

一個例子的時間,但首先,為了使事情更容易,我將模型展平為一個視圖。

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
;

從評論中使用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 ;

這寫起來很複雜,但仔細觀察後,您可能會注意到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'
;




relational-database