图书，作者，出版商和具有书架的用户的数据库模式 [英] Database schema for Books, Authors, Publishers and Users with bookshelves

问题描述

我无法找出建立表格之间关系的有效方法。我想要一个数据库的书籍，作者，出版商和用户注册和拥有书架（阅读，当前阅读，想阅读（或计划阅读））。我希望用户能够选择他们已经阅读，想要阅读或正在阅读的书籍。

P.s。我知道PK和FK在数据库表关系。

编辑：也许这是一个更好的方式：

然后我将使用状态=阅读和正在阅读） - 请告诉我这是否有效！

解决方案

图书和作者之间，因为一本书可能有多个作者，每个作者可能写了多本书。在RDBMS中，这意味着您需要一个 written_by 表。

books 和 publishers ，但是不同。任何指定的图书只能有一个发布者（除非在您的系统中，不同版本的图书被认为是同一本图书）。这里您需要的是书中的 publisher_id 外键

最后，最重要的是你在看读者/用户。他们与书的关系。当然，这也是N：M关系。我肯定希望人们阅读不止一本书（我们都知道如果你只读一个书会发生什么...）和一本书是由一个以上的人阅读。这需要一个 book_users 连接表。这里真正的问题是，如何设计它。有三种基本设计。

1. 按关系类型分隔表格。 （由@just_somebody概述）优点：您只有INSERTS和DELETES，从不UPDATES。虽然这看起来很整洁，并且在某种程度上有助于查询优化，但除了显示大数据库图表外，它大多数时间没有实际用途。




2. 一个包含状态指标的表格。 （由@Hardcoded概述）优点：您只有一个表。缺点：你会有INSERTS，UPDATES和DELETES - 这是RDBMS可以轻松处理的东西，但它有各种原因的缺陷（更多的是后面的）。另外，一个状态字段意味着一个读者在任何时候只能有一个与书的连接，这意味着他只能在 plan_to_read ， is_reading 或 has_read 状态，并假定发生这种情况的时间顺序。如果该人会计划再次阅读 ，或者暂停，然后从头开始等等，这样一个简单的状态指示器系列很容易失败，因为突然之间的人 is_reading 现在，还有 has_read 的东西。




3. 一个日志

   强>。您将每个状态作为表格中的新行插入 - 书籍和阅读器的同一组合将多次出现。您插入第一行 plan_to_read 和时间戳。另一个与 is_reading 。然后另一个与 has_read 。优点：你只需要插入行，你得到一个整洁的事情发生的年表。缺点：跨表连接现在必须处理比上面更简单的方法更多的数据（并且更复杂）。

你可能会问自己，为什么强调你在哪种情况下插入，更新或删除？简而言之，无论何时运行UPDATE或DELETE语句，您都很可能实际上丢失了 数据。在这一点上，你需要停止在你的设计过程，并认为我在这里失去了什么？在这种情况下，您将失去事件的时间顺序。如果用户正在使用他们的书籍是你的应用程序的中心，你可能很想收集尽可能多的数据，你可以。即使现在不重要，这是可能允许你做魔术的数据类型。你可以找出一个人正在阅读的速度，他们需要完成一本书的次数等等。所有这一切，没有要求用户任何额外的输入。

因此，我的最终答案实际上是一个问题：

编辑

由于可能不清楚日志的样式以及它的功能，下面是这样一个表的示例：

CREATE TABLE users_reading_log（
user_id INT，
book_id INT，
status ENUM（'plans_to_read'，'is_reading' has_read'），
ts TIMESTAMP DEFAULT NOW（）
）


而不是在设计的模式中更新user_read表，只要现在书的状态发生变化，现在就在日志中插入相同的数据，现在填写信息的年表：

  INSERT INTO users_reading_log SET 
 user_id = 1，
 book_id = 1，
 status ='plans_to_read'; 
  

当那个人实际开始阅读时，你做另一个插入：

  INSERT INTO users_reading_log SET 
 user_id = 1，
 book_id = 1，
 status ='is_reading' 
  

等。现在你有一个事件的数据库，并且由于时间戳列自动填充自己，你现在可以告诉发生了什么事。请注意，此系统不会确保只有一个is_reading用于特定的用户 - 书对。有人可能会停止阅读，然后继续。您的加入必须考虑到这一点。

I am unable to figure out an efficient way to establish relationships between tables. I want to have a database of books, authors, publishers and the users that sign-up and have their bookshelves (Read, Currently Reading, Want to Read (or Plan to Read)). I want the users to be able to select which books they've read, want to read or are currently reading.

P.s. I am aware of PK and FK in database table relations.

Edit: maybe this is a better way of doing it:

Then I shall use "Status" = (Read, Plant to Read and Currently reading) - please tell me if this is good and efficient!

解决方案

You'll need a N:M link between books and authors, since a book might have multiple authors and each author might have written more than one book. In a RDBMS that means you'll need a written_by table.

The link between books and publishers however is different. Any given book can only have one publisher (unless in your system different editions of a book are considered the same book). So all you need here is a publisher_id foreign key in books

Lastly, and most importantly you're looking at the readers / users. And their relation to books. Naturally, this is also a N:M relation. I sure hope that people read more than one book (we all know what happens if you only ever read one...) and surely a book is read by more than one person. That calls for a book_users connection table. The real question here is, how to design it. There are three basic designs.

1. Separate tables by type of relation. (as outlined by @just_somebody ) Advantages: You only have INSERTS and DELETES, never UPDATES. While this looks kind of neat, and somewhat helps with query optimization, most of the time it serves no actual purpose other than showing off a big database chart.

2. One table with a status indicator. (as outlined by @Hardcoded) Advantages: You only have one table. Disadvantages: You'll have INSERTS, UPDATES and DELETES - something RDBMS can easily handle, but which has its flaws for various reasons (more on that later) Also, a single status field implies that one reader can have only one connection to the book at any time, meaning he could only be in the plan_to_read, is_reading or has_read status at any point in time, and it assumes an order in time this happens. If that person would ever plan to read it again, or pause, then reread from the begining etc, such a simple series of status indicators can easily fail, because all of a sudden that person is_reading now, but also has_read the thing. For most applications this still is a reasonable approach, and there are usually ways to design status fields so they are mutually exclusive.

3. A log. You INSERT every status as a new row in a table - the same combination of book and reader will appear more than once. You INSERT the first row with plan_to_read, and a timestamp. Another one with is_reading. Then another one with has_read. Advantages: You will only ever have to INSERT rows, and you get a neat chronology of things that happened. Disadvantages: Cross table joins now have to deal with a lot more data (and be more complex) than in the simpler approaches above.

You may ask yourself, why is there the emphasis on whether you INSERT, UPDATE or DELETE in what scenario? In short, whenever you run an UPDATE or DELETE statement you are very likely to in fact lose data. At that point you need to stop in your design process and think "What is it I am losing here?" In this case, you lose the chronologic order of events. If what users are doing with their books is the center of your application, you might very well want to gather as much data as you can. Even if it doesn't matter right now, that is the type of data which might allow you to do "magic" later on. You could find out how fast somebody is reading, how many attempts they need to finish a book, etc. All that without asking the user for any extra input.

So, my final answer is actually a question:

Would it be helpful to tell someone how many books they read last year?

Edit

Since it might not be clear what a log would look like, and how it would function, here's an example of such a table:

CREATE TABLE users_reading_log (
  user_id INT,
  book_id INT,
  status ENUM('plans_to_read', 'is_reading', 'has_read'),
  ts TIMESTAMP DEFAULT NOW()
)

Now, instead of updating the "user_read" table in your designed schema whenever the status of a book changes you now INSERT that same data in the log which now fills with a chronology of information:

INSERT INTO users_reading_log SET 
  user_id=1,
  book_id=1,
  status='plans_to_read';

When that person actually starts reading, you do another insert:

INSERT INTO users_reading_log SET 
  user_id=1,
  book_id=1,
  status='is_reading';

and so on. Now you have a database of "events" and since the timestamp column automatically fills itself, you can now tell what happened when. Please note that this system does not ensure that only one 'is_reading' for a specific user-book pair exists. Somebody might stop reading and later continue. Your joins will have to account for that.

这篇关于图书，作者，出版商和具有书架的用户的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！