[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. :-)
