title: Handle table that has relationship one-to-many and many-to-many in relational database
body: <p>I am having interesting problem with database design but i am not able to figure it out.</p>
  <p>I have 2 tables:</p>
  <pre><code>customers
      id              unsigned int(P)
      name            varchar(30)
      ...
  
  +----+---------+-----+
  | id | name    | ... |
  +----+---------+-----+
  |  1 | Jame    | ... |
  |  2 | Tom     | ... |
  | .. | ....... | ... |
  +----+---------+-----+
  
  
  appointments
      id              unsigned int(P)
      customer_id     fk(customers.id)
      ...
  
  +----+------------+-----+
  | id | customer_id| ... |
  +----+------------+-----+
  |  1 | 1.         | ... |
  |  2 | 2          | ... |
  | .. | .......... | ... |
  +----+---------+-----+
  </code></pre>
  <p>So the relationship between <code>customers</code> and <code>appointments</code> is one-to-many (one appointment can only have one customer)</p>
  <p>However, later on i need to have another table as below:</p>
  <pre><code>tickets
          id              unsigned int(P)
          customer_id     fk(customers.id)
          appointment_id. fk(appointments.id)
          ...
      
      +----+------------+----------------+
      | id | customer_id| appointment_id |
      +----+------------+----------------+
      |  1 | 1.         | 1              |
      |  2 | 2          | 2              |
      | .. | .......... | .............. |
      +----+------------+----------------+
  </code></pre>
  <p>This <code>tickets</code> table is to keep track if the customer coming to the appointment or not. If the customer coming without appointment. Thats ok. the <code>appointment_id</code> will be set <code>null</code> but <code>customer_id</code> still need to be assigned to get customer information from <code>customers</code> table.
  However this table make the relation of <code>customers</code> and <code>appointments</code> become many-to-many.</p>
  <p>Two tables cant have to relationships at the sametime. Something not correct here and i dont know where.</p>
  <p>My question is about the <code>tickets</code> table is to keep track who is coming to the appointment. I currently cannot insert into <code>tickets</code> table cause its complaining about multiple relationship between <code>customers</code> and <code>appointments</code>. How can i redesign the <code>tickets</code> table ? i want this table to hold the customer and appointments information for both walking( appointment_id == null) or appointment (appointment_id == int) customer.</p>
  <p>Thank you.</p>