2

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 (different from self type).

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.ObjectID) | Content
Table Comments -> id | ObjectID(fk ref. Object.ObjectID) | 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).

Community
  • 1
  • 1
Cata Cata
  • 166
  • 1
  • 9
  • I assume Object1, object2 etc are types of objects defined in your parent Object table. Do you want to **Dynamically Create** new tables if new object types are added in parent table?? – jerrymouse Apr 10 '12 at 09:02
  • @jerrymouse No , for example : we have comments object and post object. Comments are allowed on post objects but not on comments objects. Thats why i need to store the types. – Cata Cata Apr 10 '12 at 09:12
  • you should have mentioned this example in your question for a better answer. In this case I think you will have to define separate tables for each object type. What does this line mean: `These child objects can have child objects only of different types.` – jerrymouse Apr 10 '12 at 09:21

1 Answers1

0

I think you can do this in single table, by defining a self foreign key.

Objects | id | parent_object_id | name
--------+----+------------------+-----------------------------
           1 | NULL             | Object1 
           2 | NULL             | Object2
           3 | NULL             | Object3
           4 | 1                | Child Object of 1 
           5 | 2                | Child Object of 2
           6 | 3                | Child Object of 3 
           7 | 4                | Sub Child Object of 1 -> 4 
           8 | 5                | Sub Child Object of 2 -> 5
jerrymouse
  • 16,964
  • 16
  • 76
  • 97
  • Yes, but is it good to store table information in another table (security reasons)? I asked this in the end of the question ? – Cata Cata Apr 10 '12 at 09:16
  • 1
    Security related questions are not trivial to answer. In your case I wouldn't worry about embedding application specific things in the database. Securing the database server should be someone else's job. – Adam Houldsworth Apr 10 '12 at 10:07
  • @AdamHouldsworth Yes i had doubts too if i should model the db to make it easyer for application rules (was thinking about performance). Ill leave this rule only on application lvl then. – Cata Cata Apr 10 '12 at 12:13