图书馆数据库设计外键

Question

我正在为我的学校设计一个图书馆应用程序。我们有2-3个主要图书馆，一些部门有自己的图书馆.我想知道这些查询是否正确？

Libraries 
ID Name
1  Walter
2  Wally
3  Maths dept library

Books
ID Book ID  BookName
1   1       Fundamentals of calculus
2   1       Mechanics 
3   2       Fundamentals of calculus
4   2       Biology

Lib_Book_LookUp
ID  libId bookId
1    1     1
2    1     2
3    2     1
4    2     2

我想解决的两个问题是：

1. 找出某一本书有多少本
2. 找出哪些图书馆有特定的书

我现在拥有的是：

Select count(Book.bookId) where book.name = "Fundamentals of calculus";

Select count(libId) from Lib_Book_lookUp, Book where Book.BookId = Lib_Book_lookUp.bookId groupBy(libId)

我对这些问题提出的问题如下：

1. 是否没有必要使用单独的查找表并将libId合并到Books表中，并将其作为外键放在book表中？
2. 这些问题看上去正确吗？

Answer 1

您的第一个查询缺少一个FROM子句。您也可以执行COUNT(*)，但在本例中并不特别重要：

 SELECT count(Book.bookId) FROM Books WHERE BookName = "Fundamentals of calculus";

要找出哪些库有特定的书籍，您不需要COUNT()聚合。相反，您需要一个带有join的WHERE子句。COUNT()会告诉你有多少图书馆有一本书，而不是哪个图书馆。为此，您需要一个返回Libraries.Name的查询。

SELECT
  /* Return library names */
  Libraries.Name
FROM 
  Libraries 
  /* Join through your lookup table to match a book name to a library name */
  JOIN Lib_Book_LookUp ON Libraries.ID = Lib_Book_LookUp.libId
  JOIN Books ON Books.ID = Lib_Book_LookUp.bookId
/* Which book to search for */
WHERE Books.BookName = 'Fundamentals of calculus'

查找表是适当的，因为它允许您将书籍规范化为Books表中的单个记录，如果它存在于多个库中。正如您所拥有的，没有真正的理由在Books表中拥有多个副本，也没有理由使用Book ID列。事实上，Book ID列具有很大的误导性。有两个不同标题的书籍具有相同的id 1。

Books表实际上应该如下所示，每本书的标题都有一个记录(假设标题是权威的，忘记真正权威的东西，比如ISBN)。

Books
ID BookName
1  Fundamentals of calculus
2  Mechanics 
3  Biology

如果每个图书馆可能有多本每本书，则可以考虑将每本书标准化为一个表，通过库条形码标识它们。然后，将这些ids与库匹配为持有量：

Books (defines bibliographic details)
ID BookName
1  Fundamentals of calculus
2  Mechanics 
3  Biology

Book_Copies (Matches Books.ID to barcode, barcode is Primary Key)
BookId Barcode
1      1234567
1      1234568
2      8654321
2      8654322

Lib_Book_LookUp (matches book copies to libraries, allowing multiple copies by barcode)
ID  libId bookBarcode
1    1     1234567
2    1     1234568
3    2     8654321
4    2     8654322

例如，要查询每个图书馆每个图书的拷贝数，您可以使用：

SELECT 
  Libraries.Name,
  Books.BookName,
  COUNT(*)
FROM 
  Libraries
  JOIN Lib_Book_LookUp ON Libraries.ID = Lib_Book_LookUp.libId
  JOIN Book_Copies ON Lib_Book_Lookup.bookBarcode = Book_Copies.Barcode
  JOIN Books ON Book_Copies.BookId = Books.ID
GROUP BY 
  Libraries.Name, 
  Books.BookName

Answer 2

您的图书表看起来更像是“图书实例”表。因此，它不应该有书名。或者是BookID列是多余的。

如果您从1988年开始编写SQL，则第二个查询是正确的。如果没有，则应该使用join语法。