4

I am designing a database for my cookbooks. I have created multiple tables in my design: books, authors, recipes, ingredients and for all these items I want to link media (images or video) to items in all these tables.

I was thinking of a design like:

media_id,
rid (primary key of foreign table),
rtype (1=book, 2=author, 3=recipe, 4=ingredient),
media_type(1=image,2=video),
media_url

But how will I ensure relational integrity?

Thanks

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
Matthijs
  • 71
  • 5

3 Answers3

5

Your proposed design seems to imply that each entity (book, author, etc.) can have multiple media files, so to maintain relational integrity, I'd have separate junction tables for each relationship.

enter image description here

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

If there's only 1 media-item for each table, the media_id should be in the tables in stead of the other way around.

If several media-items are possible you shoud link them within an extra table. There should be an extra table per item (bookid_mediaid for example).

If you think it should be linkable within one table, you are actually stating that those items have at least something in common. Otherwise the rid would have different meaning throughout the records, depending on type, and that's not possible in relational theory.

Concluding :

Your design is not good. Either you should have a relationship per entity or find what's common for all entities and use that to link to your mediatypes.

Peter
  • 47,963
  • 46
  • 132
  • 181
  • Hi Peter, it's possible that there are multiple media rows for one row in any of the other tables. – Matthijs Mar 01 '11 at 22:31
  • Then it's either option 2 (the one Joe made visual) or option 3 (isolating what is in common of the tables in a new table) – Peter Mar 02 '11 at 08:39
0

Matthijs,

For a relational design, you have to look at how these objects are related to each other and then decide on the data model. Here is an initial draft and the conditions that I am assuming. You might have different rules for your case.. post them and someone should be able to post an accurate model.

Each book can be written by many authors and each author can write different books.   ( M:N)
RECIPES <--> BOOKS (M:N)
RECIPES <--> INGREDIENTS (M:N)

These would be the initial set of tables.
BOOKS, AUTHORS, BOOKS_AUTHORS_ASC, RECIPES, BOOKS_RECIPES_ASC,
INGREDIENTS, RECIPES_INGREDIENTS_ASC.

If the media is related to one and only one book, you'll have a different table , say, media with the following columns. BOOK_ID is the parent column to which this media is associated.

MEDIA
-------------------------------------
MEDIA_ID NUMBER -- Primary-key
BOOK_ID  NUMBER -- Foreign Key..
MEDIA_TYPE varchar2(20) -- 'IMAGE','VIDEO' etc..
other_column1 varchar2(50),
other_column2 varchar2(50)
-- so on..

And if a given image/video can be associated to multiple books and each book can have different images, then you'll have a different entity for the association.. something like..

Media
--------
Media_id number -- primary key
media_type varchar2(10),
media_name varchar2(100),
--- other columns.

MEDIA_BOOK_ASC
--------------
MEDIA_BOOK_ASC_ID NUMBER,
MEDIA_ID NUMBER,  --foreign key to media table.
BOOK_ID NUMBER,   --foreign key to book table
--other columns related to associations...
Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
  • Rajesh, thanks for your comment, I overlooked the fact that a book can have different authors and will add this to my design. – Matthijs Mar 01 '11 at 22:27