SQL Database with variable number of columns

Question

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

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

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.

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?

Answer 1

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.

This require you to have 3 tables.

• 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.

Additional notes

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.

Order of appearance

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.

Answer 2

You have many to many relationship between entity Publication and entity Author. Publication can have many authors, author can have many publications.

So, you should create table for this relationship. For example table Authors_Publications with columns: UID, author_id, publication_id, order