0

I am trying to find the name of my problem:

A Comment table contains comments made by User on either a Concert or Play or Album or Painting, etc.

I am trying to avoid using many different comment tables CommentPlay, CommentAlbum!

This is perhaps a one-to-many relationship until I want to search for all Comments under a specific Play. There must be a comment_type (e.g. for_play, for_album) somewhere but I do not know how to include that in the relationship.

I am accessing the database using abstract SQL (Perl's DBIx::Class) and would like to remain db-vendor agnostic (among SQLite, MySQL, Pg).

Could someone give me a pointer as to what exactly is the name of the situation I am facing?

bliako
  • 977
  • 1
  • 5
  • 16
  • 1
    Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Apr 30 '20 at 11:43
  • "type" "many different" "type" ... My linked question [and this one](https://stackoverflow.com/q/190296/3404097) use the word inheritance but that's just a kind of subtyping & the answers & many many more are actually about subtyping generally. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Apr 30 '20 at 11:44
  • I am trying to avoid using different `Comment` tables one for each context. – bliako Apr 30 '20 at 11:57
  • 1
    Yes but 1 comment table is one one of many ways of modelling (sub)types of comments, which you do have here. Your question isn't clear. Give *some* full correct design we can rearrange & say what's wrong with it. What is stopping you from having one table of comments? If you want to limit each comment to only apply to one oher entity type/table then that is subtyping comments per whether they are in what other table(s). You can enforce referential integrity by adding to each comment in the comment table & target table a column to tag the type/target & that is 1 of the idioms in the link. – philipxy Apr 30 '20 at 12:39
  • "adding to each comment in the comment table & target table a column tagging the type/target" that is what I suggested doing in my question, though initially i thought there must be a better way, Are you a database expert? edit: btw do we have a name for this kind of relationship? or relationship where it's lookup is conditional upon table types? – bliako Apr 30 '20 at 12:44
  • 1
    What is a "relationship"? You seem to mean FK. No special name here but it is a case of adding a type variant/tag to a FK to indicate its type, creating a composite or larger FK. See the IDEF1X information modelling method/standard for its take on this. See how published information modelling methods including EER & ORM2 model subtypes. Read the many idioms at the many SO posts re DB/SQL subtyping/inheritance/polymorphism. PS In the relational model a *relation/table* represents a relation/relationship/association & pre- & pseudo-ER methods wrongly call ER participations & FKs "relationships". – philipxy Apr 30 '20 at 13:12
  • i guess not. Anyway, is there a way to query using a JOIN with additional conditions? something like JOIN play.id=comment.fkid AND comment.type='play' – bliako Apr 30 '20 at 16:04
  • 1
    A JOIN with ON has any condition. x INNER JOIN y ON c is x INNER JOIN y WHERE c. x CROSS JOIN y is x INNER JOIN y ON TRUE. If the condition you want for a join is that certain subrows are equal, write that condition. If that's your question, about querying, it's not clear from your post. But that's answered by any intro to SQL/JOIN & is (nevertheless) a faq. – philipxy May 02 '20 at 23:59
  • thanks for your help (and !wow! not a single mention of IDEF1X which I knew only as Obelix's pet-dog! :) ).That's what I was looking for with my newbie terminology. Your pointer to Table Inheritance is also of great help and I will go that way although at first I thought it extravagant. thanks. – bliako May 03 '20 at 18:11

0 Answers0