I have a database called data that is going to contain some data about writers, books and images. I've created these tables so far, but I don't know how to struct the images table. A writer or book can have more than one images, so I thought I could solve this issue by creating a table called img and attach to other tables. Is that a correct solution? If it's not, how can it be improved? I use innodb as a storage engine.
Table name : Book
book_id (Primary Key)
book_name
writer_id (Foreign Key)
Table name : Writers
writer_id (Primary Key)
writer_name
writer_bio
Table name : Images
image_id (Foreign Key for all tables that need to hold an image including book,writers, etc.)
image_url
I have an additional question. I started adding data into my tables, but didn't create the relationships yet. Would it be a problem when I create the relationships? Will the indexes be created automatically?
Best Answer
You need a one to many relationship between Book and Writers to Images.
image_id should be a Primary Key just like writer_id and book_id providing a unique id of that specific image within your data.
Than you can have book_id and writer_id fields in Images table which will hold which book or writer this image belongs to. Or another way would be to add an image_type field as enum for book or writer, and add a parent_id field where you will hold the book_id or writer_id depending on the type.
Yes, you can keep the relationship logic as part of your application away from the database, or you can add foreign constraints and relationships after adding some data given that you don't have anything that break those constraints.