Mysql database connector table or keys

Question

This may be a simple a question but I just wanted to clarify, at work I have a table containing a list of titles and I also have a list of authors in two different tables.

What would be best way to connect them? I have a connector table with the IDs of the Authors and ID of the titles so you can left join them, is there a better way to do this? i looked at having an extra column in the title table which would allow the author ID but this would be problematic for multiple titles to a single author.

Does anyone have any suggestions of a better way of doing this ( instead of a connector table)?

any help would be greatly appreciated.

Thanks

James

Answer 1

A connector table is the standard way to solve this sort of problem.

As you say in your question, if you have a TitleID column in the Authors table it restricts you to one Title per Author, unless you add a Title2ID, Title3ID, etc columns, which for someone like Stephen King might mean quite a few columns.....

Also, by having the connector table you can deal with cases where a Title has more than one Author.