Search code examples
databaserelational

Relational DB design: connecting different kind of "objects"

I am currently designing a Database for a Project of mine. The target is to connect different kind of "Objects" like Web-links, text, pictures and files with each other. Using the following tables my first attempt ended as described below:

TABLE text
TABLE picture
TABLE file

so my first attempt was to connect each Table to the Table "Project" by using n:m relations. after that I had to create a Table which safes the connections between those objects, so I created a "relation" Table. But then the Problem appeared, that I am not able to connect each table (text,picture and file) to each other very efficiently.

Does anyone of you guys now how to do such a thing in a good way? Thanks for your help in advance! CyrillC

Solution

  • N:M relation can be resolved using the following technique. Lets suppose there is an entity Student, you create a table named Student, an entity Teacher so a table Teacher is created. Lets suppose one teacher can teach many students and one student can be taught by many teachers. So, to resolve this so can introduce weak entity, lets suppose you create a table named TeacherStudent. The columns of TeacherStudent could be pkTeacherStudentId, fkTeacherId, fkStudentId.

    The same approach could be applied in your case to resolve N:M relation.

    Hope this help.