1

Various similar questions have been asked but they either aren't the same or I cannot understand the answers. So very simply please......I have various tables that have a comments field, which may contain multiple comments. Clearly the way forward is to reference a Comments table. I'm OK with that. But I can't visualize how 2 or more tables can share the same Comments table and to have Table1Comments, Table2Comments etc seems crazy. I am using phpMyAdmin and if I share the same name, it's not unique and fails.

How do I modify the following so that Table1 and Table2 both store comments in Comments?

[Table1: *table1ID]

[Table2: ?table2ID]?

[Comments: commentID, *table1ID, comment, date]

LittleC says he does this himself in this post Foreign key referring to primary keys across multiple tables? but I can't understand the answer!

Please don't close because it similar to others because I have reached an impass and need help! Thanks

Community
  • 1
  • 1
sharon
  • 55
  • 7
  • Putting all the comments into a single table just because they happen to be "Comments" is not good design. If the shared table served to tie `Table1` and `Table2` together then maybe it would make sense, but from the information you've given I don't see anything that makes a shared `Comments` table useful - quite the opposite actually. As can be seen from LittleC's answer, you're only asking for complexity, and the complexity will grow quickly if you share comments for more than two tables. – Ed Gibbs Feb 03 '15 at 22:39
  • OK if that's the case then fair point. But I have at least 10 tables, Say Table1..10 that all have a comments field. Are you saying I should create comments table, CommentsTable1..10 for each? My background is Java not databases but I thought there would be more less repetitive solution. Please confirm. Thanks in advance. – sharon Feb 03 '15 at 23:32
  • The problem with a single table is that there's no clean or easy way to be sure that a comment points back to one and only one table. It could point to two or ten or none. The alternatives are to enforce everything with triggers, which is pretty messy, or to do something like LittleC did, which would require 10 comment columns in your comments table. I don't think you'd gain much there. Do the ten tables have anything in common other than needing multiple comments? – Ed Gibbs Feb 04 '15 at 01:10
  • No they don't have much in common. Some represent people, some things, some events. The comments are "notes" rather than the type of comment left here. However I am going to have the same problem with Contracts. Various people (not related) may each reference a Contract (0..*) which although different in purpose (buying, selling), would essentially contain the same fields (i.e date, party1, party2, terms etc). If I need one table for each, fine, maybe I will understand better later, but it seems inelegant. Easier to code though. – sharon Feb 04 '15 at 08:43
  • This is a tough design for your first database project! I'm sure the answer lies somewhere near what you plan to do, with adjustments for items like comments. Also, a single table for contracts sounds reasonable based on what you say; that doesn't feel like the same situation as the comments. If you get a draft design pulled together I'd suggest posting it to dba.stackexchange.com with a "database-design" tag and (a) give a quick overview of what the database needs to do and (b) include the design. Make sure you also specify it's MySQL; some design choices will be driven by that. – Ed Gibbs Feb 04 '15 at 14:44

1 Answers1

0

I have done this (for better or for worse) in a big program that I wrote. The key (pun intended) is that you have to add a 'type' designator to your table. You wrote

[Comments: commentID, *table1ID, comment, date]

Obviously the tableID is not unique as it could refer to either table in your example. You need to add an extra field (normally but not necessarily a character) which identifies the table. Thus the structure should be

[Comments: commentID, *table1ID, type, comment, date]

In my program, tables 'dockets', 'projects', 'customers' (and probably others) store their comments in the 'comments' table. So the 'type' field has values like D, P and C in order to differentiate.

The difference between what I am suggesting above and LittleC's answer to the question which you referenced is that I am using a character to differentiate whereas s/he is using an integer. My solution is more limited but is probably clearer; it's also sufficient for my needs. The other solution requires a table of tables.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50