Skip to main content

Relationships Between Tables

  1. Ticket ↔ Tags: Many-to-many relationship via the TicketTags table.

    • A ticket can have multiple tags.
    • A tag can be associated with multiple tickets.

  2. Tags ↔ Tags: Many-to-many self-referential relationship via the TagsParentTags table.

    • A tag can have multiple parent tags.
    • A tag can have multiple child tags.

  3. Ticket ↔ Participants: One-to-many relationship.

    • A ticket can have multiple participants.
    • Each participant is associated with one ticket.

  4. Ticket ↔ DynamicParameters: One-to-many relationship.

    • A ticket can have multiple dynamic parameters.
    • Each dynamic parameter is associated with one ticket.

  5. Ticket ↔ BotConversationSteps: One-to-many relationship.

    • A ticket can have multiple bot conversation steps.
    • Each bot conversation step is associated with one ticket.

Diagrammatic Representation (Simplified)​

Notes on the Design​

  • Nullability: Fields essential to the operation of the application are marked as NOT NULL, while optional fields are nullable.
  • Constraints: Primary keys and foreign keys enforce data integrity and relationships.
  • Defaults: Some fields have default values to ensure consistent data (e.g., boolean flags, counts).
  • Data Types: Chosen to represent the nature of the data (e.g., VARCHAR for strings, INT for numbers, DATETIME for timestamps).

Example of How Tables Relate​

Scenario: A Ticket with Tags and Participants​

  • Ticket: A record in the Ticket table represents a support ticket.
  • Tags: The ticket is associated with multiple tags stored in the Tags table.
  • TicketTags: Associations between the ticket and tags are stored in the TicketTags table.
  • Participants: The ticket has participants stored in the Participants table, linked via ticketId.