1

I'm working on designing a database for a ticket management system, and I'm looking for guidance on the best database schema for my use case. The system allows tickets to be created by either a merchant or through email, and each source requires different fields to be stored.

Here are the essential fields required for the "Ticket" table:

id: The primary key for the ticket.

subject: The subject of the ticket.

body: The body of the ticket.

created_at: Timestamp for when the ticket was created.

ticket_status: The current status of the ticket.

For tickets created from email, I need to store additional data:

sender_email: The email address of the sender.

sender_name: The name of the sender.

For tickets created by merchants, I need to establish a foreign key relationship with a "Merchant" table.

My question is, should I:

Create a single "Ticket" table with conditional fields (e.g., "sender_email," "sender_name," and "merchant"), where some fields may be NULL depending on the source of the ticket, or Create separate tables, such as "EmailTicket" and "MerchantTicket," which can lead to potential duplicate code but ensures a cleaner structure. I'm trying to find the best approach to balance simplicity, maintainability, and scalability. What would be the most suitable database design for this scenario? Are there any other alternative solutions I should consider?

2
  • Definitely one table; consider all the people writing queries that don't care about the source of the ticket, but just how many open, etc... If you went with two, the users would ask for a view that would look like a single table. Oct 31, 2023 at 19:06
  • thanks, I will go with your suggestion
    – pi3o1416
    Nov 1, 2023 at 5:14

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.