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?

Improve this question
4
  • What does it mean: "A writer has an image"? Is he the painter/creator of the image or does he have a tattoo? Commented Dec 14, 2014 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. Commented Dec 14, 2014 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)? Commented Dec 14, 2014 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) Commented Dec 14, 2014 at 23:35

1 Answer 1

Reset to default
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.

Improve this answer
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. Commented Dec 13, 2014 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.