Database Design for Multi-use Table

Question

Say you have multiple "things" which can each have one or more comments attached. Product and Order, for instance. How should the tables be structured....

Product, Order, Comment, ProductComment { ProductID, CommentID }, OrderComment { OrderID, CommentID }
Product, Order, ProductComment { ProductID, Text }, OrderComment { OrderID, Text }
Product, Order, Comment { ProductID, OrderID, Text }

Using SQL Server 2008, by the way.

Thoughts, opinions?

Take a look at a **[similar question/answer](http://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523)**. — Damir Sudarevic, Nov 12 '10 at 12:17

score 2 · Answer 1 · answered Nov 12 '10 at 05:02

2

I think that the Order/Product tables should stay as is.

The Comments table can be

CommentID
EntityID
EntityType
Comment

Where EntityType will then tell you to which table the EntityID belongs (ProductID/OrderID)

answered Nov 12 '10 at 05:02

Adriaan Stander

162,879
31
289
284

Thanks. The reason I didn't list this option is because this breaks the foreign keys. I don't really want to check EntityType to determine which table to look in to fetch the Entity. – Josh M. Nov 12 '10 at 05:04
This does have the limitation that you can't use explicit foreign keys in a database that supports them (e.g. InnoDB), which means you can't really utilize features like `CASCADE`. I appreciate that it reduces the number of tables necessary, and in many cases that's good, but it's not universally worth it. – theazureshadow Nov 12 '10 at 05:05
1

I probably would lean towards this because it gives you the flexibility to add another type in the future without having to create any more ORM-type of code to support it (or add another table or field). Sure you cannot do cascades anymore, but in these specialty situations you have to make some compromises. – Chad Braun-Duin Nov 12 '10 at 06:23

score 1 · Accepted Answer · answered Nov 12 '10 at 05:11

1

Definitely only use one Comment table, so you don't have to duplicate Comment information (e.g. timestamp, flagged_for_moderation, etc). Having two fields in comment is nice because it makes it clear that it's a one-to-many link. I'd probably lean towards that over multiple linking tables, though I do appreciate that you only have rows in the linking table when there's a link, versus having half the values be NULL. Perhaps in a very large database with more things that can be commented, you might go for the linking tables.

answered Nov 12 '10 at 05:11

theazureshadow

9,499
5
33
48

Agreed about the duplication aspect. I like the linking tables except I don't like the extra step to get to the Comment table. – Josh M. Nov 12 '10 at 05:20
Yeah, the linking table does make your queries more complicated. Using multiple columns instead also means that if you get all Comments, you can tell what kind of comment they are without doing any JOIN at all, and if you use IDs in URLs (or other references), you may be able to just link directly to the item being commented on. – theazureshadow Nov 12 '10 at 05:25
Another reason I like the linking table is that you can add additional fields as needed. Maybe the ProductComment table also has some fields which are related strictly to that combination of Product\Comment. – Josh M. Nov 12 '10 at 05:30
1

I would say use the linking table if you need the extra fields or if you expect to soon. It's really hard to future-proof a schema. Also, just as a side note, I often use the convention Product_Comment for linking tables, to distinguish between them and normal camel-cased table names. – theazureshadow Nov 12 '10 at 06:01
I think the linking table option is the most flexible and future-proof. And these tables can be treated as pass-through tables since they are really one-to-one. So code could be generated to expose the Comment table directly on the Product table, skipping the linking table altogether. – Josh M. Nov 12 '10 at 18:11

score 0 · Answer 3 · answered Nov 12 '10 at 05:36

0

If you don't like the entityID, entityType method, because you can't use foreign key constraints, you could take a mixed strategy, something like

COMMENT(commentID, comment, productID, orderID, ....)

with ...'s as an additional column for each commentable table.

answered Nov 12 '10 at 05:36

SingleNegationElimination

151,563
33
264
304

this maybe could work, but astander's answer is how it is normally done. – SingleNegationElimination Nov 12 '10 at 05:37
This is my option #3. Not sure if I like it though....I'm leaning toward option 1. – Josh M. Nov 12 '10 at 05:38
@TokenMacGuy I think astander's answer is worse than the 3 options I've listed. – Josh M. Nov 12 '10 at 05:39
@TokenMacGuy Can you provide some examples showing that this is how it is "normally" done? ORMs like Doctrine don't even *support* methods like that in their built-in many-to-many relationships. And there are some obvious disadvantages. Needless to say, I am dubious. – theazureshadow Nov 12 '10 at 05:50
well, the ORM in both Django and Rails works in the manner suggested by astander – SingleNegationElimination Nov 12 '10 at 06:45
@TokenMacGuy: Well, Django *supports* that. You have to explicitly define a [Generic relation](http://www.djangoproject.com/documentation/models/generic_relations/). But still, good examples. I should figure out how much of a speed difference that would entail. – theazureshadow Nov 12 '10 at 18:43
Django supports it exceptionally well, with the `django.contrib.contenttypes` app, which is in the vanilla distro. – SingleNegationElimination Nov 12 '10 at 22:18

score 0 · Answer 4 · answered Mar 08 '11 at 17:08

If you go with the linking tables and you have more than 2 or 3 “types” that can be linked to comment then start thinking about a code generated to create all the SQL you need. You will very soon have a 101 linking tables and lots of table definition SQL to maintain.

If you use GUIDS for all your IDs and don’t mind not having foreign keys defined in the database, then there are other options, but I don’t think that the style of database schema you have.

This is one of the use cases that make me think the relational model is a pain in the neck!

score 0 · Answer 5 · answered Apr 19 '11 at 13:55

0

I am a fan of adding the entity ID and entity type columns to add add flexibility without having the additional join.

answered Apr 19 '11 at 13:55

skaz

21,962
20
69
98

Thanks for the suggestion but I find this to be a pretty dirty, non-OO way of structuring a database. You're losing the DB logic to validate that the entity actually exists. Of course you can do this in code, but from a DB standpoint it is not a very clean solution. – Josh M. Apr 19 '11 at 14:52
@Josh M - You will have to consider the space trade off. Not everything can be perfect. This is a very OO way of doing things - the Entity is polymorphic as it represents many different types in one table. – skaz Apr 19 '11 at 14:54

Database Design for Multi-use Table

5 Answers5