db scheme question...

[eluser]crumpet[/eluser]
Hi, I am working with a website where there are many different content types ie survey, article, news, etc. I want each of these to have a comments feature. Is there any problem with creating one comments table which has a field called type (survey, article, news) and then parentID and then comment ID. This way I dont have to create a comments table for every single content type.

Any problems that you can for see with this approach ? will it be slow?

Thanks,

[eluser]missionsix[/eluser]
You should use a single comments table, but your thinking about it wrong.

What you want here is a many to many relationship between tables.

So lets say you have articles, and want to add comments to them. you should have an intermediate table that stores the relationships between articles & comments.

Code:

+- Articles -+
--------------
-- id
-- ect..
--------------

+- Article_Comments -+
----------------------
--  Article_id
--  Comment_id
----------------------

+- Comments -+
--------------
-- ID    
-- comment
-- ect...
--------------

So you would create the relationship table for every thing that you want to add comments to. Then, you can add to your model, a way to retrieve comments using a joins based on comment_id from that table.



Hope that helps!

[eluser]crumpet[/eluser]
ok that makes a lot of sense - i was hesitant to be referencing things by the table names instead of id numbers and this solves that problem

many thanks

[eluser]Teks[/eluser]
@crumpet: you will still have to identify the table, but that is not a big issue.

If I understood correctly, what you are trying to do is pretty standard in database design, and it is called a one-to-many 'polymorphic' association. It is not a 'many-to-many' relationship, because each comment can have only one single 'owner' - that is, one single article/news/survey where the comment will appear. The relationship would need to be a 'many-to-many' only if a single comment were to belong to several articles/surveys/newspieces simultaneously - and that does not seem to make much sense to me.

The relationship between the comments and the other tables needs to be 'polymorphic', precisely because you don't know the type of 'owner' the comment will have. For each comment record, the 'owner' might be an article, or a survey, or a newspiece, so storing the id of the owner *only* is not enough. This means, that you *do* need an 'owner_type' field in the database, in order to be able to properly relate the comment to the appropriate owner, in the appropriate table.

Supposing that you have tables named 'articles', 'news', 'surveys' and 'comments', here is roughly how I'd do it:

Code:

------------------------------
articles
------------------------------
- id
- etc.
------------------------------


------------------------------
news
------------------------------
- id
- etc.
------------------------------


------------------------------
surveys
------------------------------
- id
- etc.
------------------------------


------------------------------
comments
------------------------------
- id
- owner_id
- owner_type
- etc.

+ index owner_id, owner_type
------------------------------

If you build an index of owner_id, owner_type, your queries will be sufficiently fast, even if your data set is large. You will have less tables in your database, and you will be able to avoid lots of complicated join queries, which you'd have to have, if you were to try and avoid using the 'owner_type' field.

I hope this helps. :-)

[eluser]crumpet[/eluser]
Does it matter if I store owner_type as just the name of the table like 'articles' or should i create aliases for these ie owner_type '1' 1 = 'articles' .. this is of course just a question of speed

[eluser]Teks[/eluser]
@crumpet: in most modern database implementations (SQLite, MySQL, PostgreSQL) there will be *very, very little* noticeable difference, even on very large data sets.

I would tend to use the table names, because once table names are set, I don't normally change them - it interferes with too much - and also, a few months from now, if I look at the data in the database I will understand what 'article', 'survey', 'news' means (but it would take me several minutes of code archaeology to figure out what '1', '2', 3' is supposed to mean...).

[eluser]crumpet[/eluser]
thanks for all the help