0

I am creating a relational DB schema for a ticketing system. Ticketing system will have two different types of tasks (tickets), which I would say I have to keep in separate database tables. However, both types of tasks will have notes, and notes will have the same properties, regardless of type of task they belong.

Relations between Task1 and NoteForTask1 as well as Task2 and NoteForTask2 are one to many (one task can have 0 - n notes).

My idea how Task1 and Task2 tables should look like is (remember that Task1 and Task2 have completely different properties):

Task1
  Task1ID <PK> 
  Task1Property1
  Task1Property2
  etc...

Task2
  Task2ID <PK>
  Task2Property1
  Task2Property2
  etc...

Since notes for both tasks have the same properties, the NoteForTask1 and NoteForTask2 tables should look like this:

NoteForTask1
  NoteID <PK>
  Task1ID <FK>
  NoteText

NoteForTask2
  NoteID <PK>
  Task2ID <FK>
  NoteText

Since Note1 and Note2 have completely the same properties, I suppose it would be ok to keep them in the same database table. But in that case foreign keys for Task1 and Task2 will overlap, since Task1ID and Task2ID both have their own sequence, so from the foreign key TaskNID itself, one cannot tell if that is ID of Task1ID or Task2ID.

How to structure the DB schema to keep Task1 and Task2 in two separate tables, but to have notes in one table? Or that is completely wrong approach and I should keep two different types of notes in two separate tables?

Marko
  • 41
  • 3
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Sep 08 '18 at 00:31
  • Hi. This is a faq. You have "*two different types* of notes". Google sql/database subtyping/inheritance. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. – philipxy Sep 08 '18 at 00:31
  • Thanx, @philipxy, the article you have mentioned, now when you pointed that out, will help. The problem menitoned in the article is not exactly the same one I have, but the principle is the same, so it helps. – Marko Sep 09 '18 at 17:56
  • @Marko What you need in the NOTES table is 2 columns `notable_type` and `notable_id` where notable corresponds to Task1 or Task2. This is a very common practice. – Sarvnashak Sep 10 '18 at 20:06

0 Answers0