Hello everyone:

I have two database desgin questions. I hope could get some helps here to resolve my puzzle.

suppose I have a parent table which has many-to-many relationship to its three children tables. E.g:

Parent table:
webpage
-pageID
-pageTitle

child table: (which are the content could be in the webpage)

Picture
---pictureID
---pctureName

StreamVideo
--videoID
--videoName

StreamAudio
--audioID
--audioName

Normally, in a many-to-many relationship, a third table will be introduce to break down the many-to-many into one-to-many relationship. So in this case, I will have three relationship tables

1. Webpage--Picture
--pageID
--pictureID

2. Webpage--Video
--pageID
--videoID

3. Webpage--Audio
--pageID
--audioID

My question is should I combine this three relationship table into one big relationship table like:

Webpage--content
--pageID
--pictureID
--videoID
--audioID

I know the drawback of this combining will cause redundant, but this could avoid join tables when writting query (especially when there are more child table associate with one parent table). Also I was told that foreign key can be nullable, so if one webpage only have pictures, as long as I insert pictureIDs with the null value videoIDs and audioIDs into the Webpage--content table, then it seems I don't duplicate anything. So far I don't see anything wrong with this approach, I hope someone can give me some suggestions whether I should go this way.


My another question is also about the relationship table, suppose in the above exmaple, we won't combine those three relationship tables. But we need to assign a value to each pictureID, videoID, audioID. And this value will be different if a pictureID, videoID, aduioID appears in different webpage. How I should solve this issue? The best I can think so far is add a value column in the relationship table. But I have not seen people did this before. Add columns into a relationship table. Is this a good desgin?