Practical design for table field referencing more many tables.

Question

The challenge I am facing is trying to come up with a good model design for a common table. A table "notes" is to be referenced by different objects. One way, I thought is to define 2 fields object type, object Id to record the type and the object the note belongs to. Then I am not sure this is the best practice. When it comes to generate a list of notes and the name of the objects they belong to I have to do a query for every object type and join them all together with a union. Another approach that crossed my mind was to add as many fields as there are object types and simply join all these tables to notes in the query. I would appreciate any comments on the matter.

Answer 1

If there is a maximum of one note per object, add a nullable noteid field to each object's table.

If there is no such limit, the most normalized approach is to create a linking table for each distinct object type table containing noteid and objectid, with the pair of identifiers as the primary key and each of them referencing its corresponding table; this also accommodates the case where one object is linked to more than one note, or one note is linked to more than one object. Furthermore, no nullable fields are introduced, but the queries do become more complex. For performance, you may need to index each such table both ways.

Type-coding (that is, including a column specifying the applicable type), while a very common practice, significantly weakens the integrity of the data model. Even if you use a permitted-values table, you still have unchecked duplication between the rows in the permitted-values table and the definitions of the linked tables.

Answer 2

Your first method is correct I believe. Either that or an M2M table sitting between the two tables which keeps track of the relationship.

From a strict data model standpoint having the type in the notes table is wrong, because a note isn't specific to a type, it's just a note. So keeping that info in the M2M table feels more correct. However M2M is harder and depending on your framework this may be inadvisable.

Hope that helps!