Relate Reply Table to Ticket Table

Question

Assume i have a ticket/question that can have zero or many replies.

I can relate 'reply' and 'ticket' tables in two ways.

First method:

Ticket(**ticket_id**, title, message) //ticket_id: PK
Reply(**reply_id**, reply_message, ticket_id) //reply_id: PK and ticket_id: FK

Second Method:

Ticket(**ticket_id**, title, message) //ticket_id: PK
Reply(**reply_id**, **ticket_id**, reply_message) //reply_id: PK and ticket_id: FK & PK

In my opinion, both of them are correct.

As i see it, the Reply in second method is considered Weak entity because reply is strongly tied to ticket. However, i prefer the first method because its easier to deal with at the programming level; in first method, we only have to handle one PK. Do you agree? Why and Why Not?.

NOTE: ticket and reply are mock-up samples.

Answer 1

As i see it, the Reply in second method is considered Weak entity...

Correct.

...because reply is strongly tied to ticket.

Not sure what you mean by that. It is weak because it cannot be identified without attributes migrated from the parent.

However, i prefer the first method because its easier to deal with at the programming level; in first method, we only have to handle one PK. Do you agree? Why and Why Not?.

Well, it depends ;)

The strong entity and non-identifying relationship (your "first method"):

• May be friendlier to client code, especially if you use ORM.
• Prevents the parent PK from propagating further down the table hierarchy¹, which:
  • Will make child FKs leaner.
  • Will "cut" the ON UPDATE CASCADE and prevent it from propagating further down the table hierarchy.

The weak entity and identifying relationship (you "second method"):

• Results in a PK that is a good candidate for a clustering key.²
• Avoids additional index on FK³, since primary index covers both fields⁴. Every additional index costs space and performance (when modifying data).
• Propagates the parent PK down the table hierarchy¹, which:
  • May allow you to avoid some JOINs.
  • May be necessary for correctly modeling certain kinds of diamond-shaped dependencies.

All in all, neither solution is absolutely "better" - it's a matter of choosing the right balance.

---

¹ Not (yet) relevant in your case, since you don't (yet) have any tables that reference Reply.

² Many (but not all) DBMSes allow clustering only on the PK. In your case, clustering on {ticket_id, reply_id} (note the order of fields) would store all replies connected to the same ticked physically close together, dramatically reducing I/O for certain kinds of queries.

³ Which would otherwise be necessary for efficiently enforcing the referential integrity when deleting or modifying rows from Ticket.

⁴ Assuming you change the order to {ticket_id, reply_id}.