书籍 - 文章 - 图像的数据库模式建议

I have the following models.

我有以下型号。

Book has Articles (Article has foreign key to Book)

Article has Images (Article has upto #max number of foreign keys to images, Article have #max number of foreign key to images. which maybe null)

Furthermore I have Book - Image relation 
because certain images are included in the book but not owned by article

I made the models to answer fast to queries such as

我让模型快速回答诸如此类的查询

Give me images for an article.
给我一篇文章的图片。
Give me images for a book (sorted bytime, paginated if needed)
给我一本书的图像（按时间排序，如果需要，可以分页）

People suggested I have a bad modeling. (when foreign key points to something else, move other data as well)

人们建议我的模型不好。（当外键指向别的东西时，也移动其他数据）

Want to know better alternative schema.

想要了解更好的替代架构。

1 个解决方案

#1

I would opt for the simple solution of (assuming postgresql database):

我会选择（假设postgresql数据库）的简单解决方案：

create table book (
  id integer primary key
);

create table article (
  id integer primary key,
  id_book integer references book(id)
);

create table image (
  id integer primary key,
  id_article integer references article(id),
  id_book integer references book(id),
  check ((id_article is null or id_book is null) and
    (id_article is not null or id_book is not null))
);

Check constraint does not allow for image to be assigned to both article or book.

检查约束不允许将图像分配给文章或书籍。

If image comes from article => id_article filled
如果图像来自article => id_article filled
If from book only (not from article) => id_book filled
如果仅从书本（不是文章）=> id_book填写

Give me images for a book

给我一本书的图像

select *
from book b
left join 
 (select *, id_book as book from image where id_article is null 
  union all
  select i.*, a.id_book as book from article a join image i on i.id_article=a.id) i on i.book = b.id;

#1