Hello, I have been discussing database design in my developer forum and we can't seem to decide which is a more pure. Any thoughts would be appreciated.

scenario i have a comment object and want to be able to associate it, to any given number of objects, more could be added in the future.

Is it better to
A: add the FK to the comment table? 1:M
or
B: Create an association table ? M:M
or
C: Other?


Solution A:

comment table
----
id
comment
blog_id
photo_id
user_id


Solution B:

comment table
----
id
comment

blog_comment_assn table
----
blog_id
comment_id

photo_comment_assn table
----
photo_id
comment_id


With Solution A, it seems that you waste space by adding the extra columns and then to find out which one it is related to you have to check each key.

With Solution B, you obviously create extra tables, but no extra space. But you change the relationship from 1:M to M:M

I apologize if this has been asked. I am trying to read up on normalization and can't seem to understand which method is best. 1NF suggests don't waste space.

Thanks in advance for the help!

I'd probably pick option C. Put the id into the comment table, then use the commentId as an FK in the tables that need a comment.

The exception to that choice would be if there were many tables that had dependancies on each other that needed to reference a single set of comments (in other words, you truck chassises that had engines in them and wheels on them, and you needed to logically tie one comment to all of the pieces in a given puzzle). Then I would give more thought to a set of linking tables.

-PatP

I'd use GUIDs instead of Identity values. That way you can store the foreign keys from multiple object tables in a single column of the comments table.

better yet, if the database supports it, use a sequence.

aaaaaaaaaaaarrrrrrrrgggggggggghhhhhhhhhhhh

there goes the idea of actually defining it as a foreign key



the correct solution, of course, is D -- have a separate comment table for each object type

Thanks for the quick replies. I am using MySQL so I am looking into GUID's and Sequences now.

Would there be a noticable performance hit in using GUID's or Sequences. GUID's seem like large id to join with?

Looks like mysql has a UUID() function so I will compare that to the GUID and see if that will work me.

Am I correct to assume that Option A below is not normalized or in normal form because it wastes space?

Option B is bad because I am trying to represent a 1:M using a M:M form?

I want to understand the whole thing, instead of just taking the first answer and running! Thanks again!

Thanks r937,

So just to clarify, Option D would look something like and that would be proper normal form?

blog_comment
-----
id
blog_id
comment

user_comment
-----
id
user_id
comment


My dilemma is this. Comments seem like they would be their own object and havinvg multiple tables in the Option D would create seperate objects for each comment type.

What about Solution E

comment table
----
id
text

comment_type table
----
id
type

comment_lookup table
---
comment_id
child_id
type_id
user_id

perhaps you could take a step back and create some sample rows which actually relate the comments to the objects

this will be a first-order proof-of-concept for you

i mean, let's look at your suggestion for the blog comment table --sample rows for this would be

id comment
42 no that's not true
43 fifty two
44 yes, i agree with todd
45 i never said that

see anything unusual? you can't tell which blog entry each comment is associated with!!

hey r937,

yea sorry about that. i did catch it and edit my post, but apparently not quick enough =)

so.. Option D would be like
blog_comment
-----
id
blog_id
comment

user_comment
-----
id
user_id
comment

yep, dat's da ticket!!!

Is it pirate day today? I forgot both my cutlass and my mascara...
There are drawbacks to using separate tables as well. I've used both methods, and the decision really comes down to the details of the requirements for each particular application.

I actually agree with the blindman on this!!

It's all well and good saying use multiple tables just to preserve your foreign keys but it comes at a cost. All your code now has to support multiple tables rather than just one. This means more time to produce the code, more effort in maintaining it. You're also quite likely to get more bugs where one query is pulling comments from most of the comment tables but perhaps missing the last comment table that recently got added.

More tables means more code!

Mike

so use a punch card file system, and you're golden

only one "table" so the code will be at an absolute minimum

and no pesky FKs to worry about



and with that, this is another thread i'm leaving because of where it ended up

I'm not even going to comment... Except for this of course. Oh, and that.

No more comments after this!

I'm sure Rudy will never let me live this down...