DataBase设计：一对多数据库？ [英] DataBase design: one to many database?

问题描述

我对数据库设计非常新。

我有疑问。问题是非常基本的。但请帮助我。

假设我在一个表中有书，而在另一个表中有作者（假设一本书是由一个作者写的） （一对多），一个作者可以写多本书（多对一））。
我不知道如何链接表和什么应该自动递增？

  tblBooks //表1包含两个实体
 {
 bookId //此字段自动递增
 bookName 
} 
 
 
 tblAuthors //表2 
 {
 authorId //此字段是否也应该自动递增？ 
 authorName 
 Field3 //那么什么是Field3，那么它作为两个表之间的链接？ 
 //什么是外键？这里'Field3'将是外键还是什么？ 
 
} 
  

帮助赞赏

 
 
 code> tblBooks {
 bookId 
 bookName 
 authorId 
} 
 tblAuthors {
 authorId 
 authorName 
} 
  

查询示例

  // Grabs每本书由一个作者做的id为1 
 SELECT * FROM tblBooks where authorId ='1'
 
 //抓取作者的书223 
 SELECT * FROM tblAuthors where authorId =（SELECT authorId FROM tblBooks WHERE bookId ='223'）
 
 //加入表格，每本书都会显示它的作者
 SELECT 
 tblBooks.bookId，
 tblBooks.bookName，
 tblAuthors.authorName 
 tblAuthors.authorId 
 FROM 
 tblBooks 
 JOIN 
 tblAuthors 
 ON 
 tblBooks.authorId = tblAuthors.authorId 
  

语法可能会根据你使用什么数据库（mysql，oracle，sqlite等），但是它的基本结构。

如果你决定使用多对多结构一对夫妇的事情，一个创建第三个表，工作链接两个表例如有许多作者的图书：

  tblBooks {
 bookId 
 bookName 
} 
 
 tblAuthors {
 authorId 
 authorName 
} 
 
 tblBookAuthors {
 bookId 
 authorId 
} 
 

或在其中一个表中具有逗号分隔的作者ID字符串的字段：

  tblBooks {
 bookId 
 bookName 
 authorIds 
} 
 
 tblAuthor {
 authorId 
 authorName 
} 
  

authorIds 会像 1,12,32 ，在这种情况下，你必须使用数据库函数来选择作者IN，例如MYSQL有 find_in_set（tblAuthors.authorId，tblBooks.authorIds）其中第一个参数是搜索，第二个是您正在搜索的数据集

并且决定多对多结构中的哪个表获得具有逗号分隔的ID的字段的方式是经常删除外部标识符的表，例如作者通常不会被移除或添加到一本书，所以它获得列表字段。

I am very new to database designing.

I have a doubt. The question is very basic. But please help me out. i will try to expain it through an example.

Suppose, I got books in one table and their authors in the other(assuming that one book is written by just one author(one to many) and one author can write many books(many to one)). I am not getting how exactly to link the tables and what should be auto-incremented?

tblBooks     //Table 1 contains two entities
    {
     bookId      // This field is auto-incremented
     bookName
    }


tblAuthors    //Table 2
    {
     authorId    // Should this field also be auto-incremented?
     authorName
     Field3      // What should be the 'Field3' then which acts as link between both the tables?
                 // What exactly is foreign key? Here 'Field3' would be foreign key or what?

    }   

Help appreciated

解决方案

The "Many" table gets a foreign key of the "One" table.

tblBooks {
    bookId   
    bookName
    authorId
}
tblAuthors {
    authorId  
    authorName
}  

Example queries

//Grabs EVERY book that was made by an author with id 1
SELECT * FROM tblBooks where authorId='1' 

//Grabs the author of the book with id 223
SELECT * FROM tblAuthors where authorId=(SELECT authorId FROM tblBooks WHERE bookId='223')

//Joins the tables so each book will show its author
SELECT 
    tblBooks.bookId,
    tblBooks.bookName,
    tblAuthors.authorName
    tblAuthors.authorId 
FROM 
    tblBooks 
JOIN 
    tblAuthors 
ON 
    tblBooks.authorId=tblAuthors.authorId

The syntax will probably change depending on what database you use (mysql,oracle,sqlite etc) but thats the basic structure.

And if you decide to go with a many-to-many structure you can do a couple things, one create a third table that works to link both tables e.g. Books that have many authors:

tblBooks {
    bookId
    bookName
}

tblAuthors {
    authorId
    authorName
}

tblBookAuthors {
    bookId
    authorId
}

OR have a field in one of the tables that has a comma seperated string of author ids:

tblBooks {
    bookId
    bookName
    authorIds
}

tblAuthor {
    authorId
    authorName
}

authorIds would be like 1,12,32, in this case you would have to use a database function to select the authors IN that set, for example MYSQL has find_in_set(tblAuthors.authorId,tblBooks.authorIds) where the first argument is the search and the second is the set of data you are searching

And the way to decide which table in the many-to-many structure gets the field with the comma separated ids is the table where the foreign ids dont get added removed often, for example the authors dont usually get removed or added to a book so it gets the list field.

这篇关于DataBase设计：一对多数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！