SQL数据库具有可变的列数 [英] SQL Database with variable number of columns

问题描述

我有一个关于我要创建的数据库的新手问题.我有一份出版物列表，按这种一般顺序排列:

I have a newbie question about a database I am trying to create. I have a list of publications, with this general order:

UID，作者，URL，标题，出版物，开始页面，结束页面，数量，年份

UID, Author, URL, Title, Publication, start page, end page, volume, year

然后我意识到有多个作者，因此我开始尝试规范化多个作者的数据库.然后我意识到作者的顺序很重要，而且期刊文章中也可能有许多作者，人数在1到数十名之间，甚至可能更多.

Then I realized that there are multiple Authors and I began trying to normalize the Database for multiple Authors. Then I realized that the Order of the authors is important, and that a journal article could also have numerous Authors, between 1, and dozens, or possibly even more.

我应该只创建一个包含多个Authors(空列)的表(例如12左右)吗?还是有一种方法可以根据作者数量来改变列数?

Should I just create a table with multiple Authors (null columns)(like 12 or something)? Or is there a way to have a variable number of columns depending on the number of authors?

推荐答案

数据库模型

您基本上需要在作者和出版物之间建立many-to-many关系，因为一个作者可以撰写许多出版物，而一个出版物可以由多个作者撰写.

Database model

You basically need a many-to-many relationship between Authors and Publications, since one author can write many publications, and one publication can be written by more than one author.

这需要您有3张桌子.

• 作者-有关每位作者的一般信息(无publications_id)
• 出版物-有关每个出版物的一般信息(无author_id)
• AuthorPublication-引用表Author和Publication的列author_id和publication_id.

• Author - general info about every author (without publications_id)
• Publication - general info about every publication (without author_id)
• AuthorPublication - columns author_id and publication_id that are references to tables Author and Publication.

这样，您就不会将特定作者绑定到出版物，但是您可以拥有更多的作者，反之亦然.

This way you're not binding a specific author to a publication, but you can have more of them, and the same thing the other way around.

如果您想区分作者在特定出版物中的角色，还可以添加一些列，例如id_role，这将是对字典表的引用，指出了作者的所有可能角色.这样，您可以在主要作者，合著者等之间有所不同.这样，您还可以存储有关处理本书翻译的人员的信息，但是也许您应该然后将Author的命名更改为不太具体的名称.

If you would like to distinguish authors' role in particular publication you could also add some column like id_role that would be a reference to a dictionary table stating all possible roles for an author. This way you could differ between leading authors, co-authors etc. This way you could also store information about people handling translation of the book, but perhaps you should then change the naming of Author to something less specific.

您可以通过在AuthorPublication中添加一列来确保作者的正确排序，该列将针对每个Publication分别增加.这样，您将能够保留所需的顺序.

You can ensure a proper ordering of your authors by adding a column in AuthorPublication which you would increment separately for every Publication. This way you would be able to preserve the ordering as you need it.

这篇关于SQL数据库具有可变的列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！