1

In the project I'm developing I've got several "common objects" that span and associate several other tables.

Think, for example, at the object "Comment". It should be applicable to many kind of different objects: a photo, an action, an event... and it always haves the same structure (author, text, insertion_time, ...)

The first solution I adopted was to have separate tables for each kind of comment: PhotoComments, EventComments and relate those to pertinent objects with a one-to-many relationship with (for example) a photo_id column.

The second (and current one) consist in having a single Comments table (each with its own id) and have as many as needed "many-to-one" support tables to relate those comments with (ie) their photo.

Are there any downsides in having such a design?

Onip
  • 23
  • 6

2 Answers2

0

If you load the data a comment belongs to, and then look for comments assigned to it, then a single comments, with a one to many works well.

If however you want to find the entity a comment belongs to then one to many tables becomes painful, as you have to look through all the link tables to find what a comment belong to. Of course you could add another column to your comments table to indicate what entity type it belongs to and then you know which link table to go to. From the sounds of things your comments don't belong to multiple entities, which removes that complication.

I'd go with the single comments table (and probably move author to a table of its own, so you can easily see which comments belong to one author, without duplicating the author information in each record)

blowdart
  • 55,577
  • 12
  • 114
  • 149
  • As this will be a demo of the product I think I'll stick with the already implemented "common table" design. – Onip Nov 28 '11 at 08:35
0

One downside I can think of comes from table locking.

Say there is a query for a photo comment. Depending on your setup, the table could lock in order to retrieve this photo comment. Then say another query comes for an action comment. If the table is locked down for the photo comment, this new query has to wait for it to complete.

Depending on the size of the table and how often queries are done for data in it, this table could become a performance bottleneck within your schema. If you don't think this could become an issue, then doing a single table can be easier to maintain. However, if there will be a lot of contention for comments, then splitting up the tables will help you out.

JesseBuesking
  • 6,496
  • 4
  • 44
  • 89