Tuesday, April 10, 2012

Best way to mix polymorphic association and hierarchies?

Assuming the base table design (no. 3) from this link:



What is the best way to implement Polymorphic Association in SQL Server?



enter image description here



I would like to add (to the object table) a ObjectType and a parentObjectID for each object.ObjectID. I need this because I have objects that are children to other objects. These child objects can have child objects only of different types.



So is it a good idea to put the names of the tables in the objectType column? This may be bad for security to store some of the schema in a table... Any other approach that I can use?



Here is an example :



Table Posts -> id | ObjectID(fk ref. Object.OnjectID) | Content

Table Comments -> id | ObjectID(fk ref. Object) | Posted_on_ObjectID(fk ref. Object.ObjectID) | Content



Basically posts and comments will be unique entities in Object super table . But because the comments can be made only for post entities and not for comments entities i have to store the types for each entity(ObjectID).





No comments:

Post a Comment