1

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?

4
  • What does it mean: "A writer has an image"? Is he the painter/creator of the image or does he have a tattoo? – miracle173 Dec 14 '14 at 2:44
  • I am sorry, I was trying to say that a writer can has more than one photos (like profile photos), not images. – salep Dec 14 '14 at 10:58
  • So these are photos of the writers , e.g. pictures where you can see the face of the writer? The pictures that you assign to a book are pictures contained in the book or are these pictures that show the book (as the pictures of the writers show the writers)? – miracle173 Dec 14 '14 at 22:49
  • Yes, exactly. Should I use junction table in that case? It would be very easy for me to use since I've just learned foreign key thingy, but I am not sure whether is wise or not. tablename : whoisthis writer_id (Foreign key from writer table) photo_id (Foreign key from photo table) – salep Dec 14 '14 at 23:35
1

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.

1
  • Thank you. It's kinda confusing since I am new, but I think I got it. I can't up vote your answer because up voting answers requires 15 reps, which I haven't gained yet. – salep Dec 13 '14 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.