I have a db design question that I am sure has been quite extensively covered but I cannot find a good answer already. The problem is how to efficiently link a single school course to a collection of textbooks, a course may have 0 books and it may have 10. The problem here is that multiple courses can use the same textbook as well. My first thought was to include the textbook tables PK as a FK in the courses table. Then the courses table has a composite PK of course id and textbook id. The problem with this approach is that the courses table could potentially have a lot of columns, and then I am repeating the entire course row for every textbook addition, in addition to having to set a fake textbook_id for courses that do not have a textbook. coursescourse_idcourse_nameFK textbook_idPK(course_id, textbook_id)textbooktextbook_idtextbook_name My next thought was to have a table that has 10 columns for textbooks, that I use for linking a course to all the textbooks. But, I'm not sure if there is a better approach (this will end up using less space), when I think about this I think why not have textbook1, textbook2 columns in the courses table in the first place. coursesPK course_idcourse_nameFK textbooks_idscourse_textbookFK course_idFK textbook_1FK textbook_2FK textbook_3FK textbook_4textbookisbntextbook_nameA bit of background as well, using MySql, Hibernate ORM, and writing a separate search indexer. Anyone have any suggestions on a better approach or thoughts on which of these two approaches is better? Thanks in advance for the help
DB Design: How to link a single column to a collection of entities
design;database;mysql;orm
You are certainly getting close with your last thought. When you start to think about a class could have at most ten textbooks, you're complicating the problem more than you need to.  Instead of having multiple columns for the course_textbook table, stick with one for textbook and one for course. This is a many-to-many mapping, and that's exactly what you want: the ability to pick any course, and find every book it uses, or pick and book, and find every course it uses. Typically, in database design, we care about only three conditions: 0, 1, and many. Your SELECT statement would then join on the table, returning multiple rows if there are multiple books.