title: Many to many relationship with different data types
body: <p>I am trying to create a Database for different types of events. Each event has arbitrary, user created, properties of different types. For example "number of guests", "special song to play", "time the clown arrives". Not every event has a clown but one user could still have different events with a clown. My basic concept is</p>
  
  <pre><code>propID | name | type
  ------ | ---- | -----
      1  |#guest| number
      2  |clown | time
  </code></pre>
  
  <p>and another table with every event with a unique eventID. The Problem is that a simple approach like</p>
  
  <pre><code>eventID | propID | value
  ------  | ------ | -----
       1  |    1   |  20
       1  |    2   |  10:00
  </code></pre>
  
  <p>does not really work because of the different DataTypes.</p>
  
  <p>Now I thought about some possible solutions but I don't really know which one is best, or if there is an even better solution?</p>
  
  <p><h1>1.</h1> I store all values as strings and use the datatype in the property table. I think this is called EAV and is not considered good practice.</p>
  
  <p><h1>2.</h1> There are only a limited amount of meaningful datatypes, which could lead to a table like this:</p>
  
  <pre><code>eventID | propID | stringVal  | timeVal | numberVal
  ------  | ------ | ---------  | ------- | --------
       1  |    1   |   null     |  null   |    20
       1  |    2   |   null     |  10:00  |   null
  </code></pre>
  
  <p><h1>3.</h1> Use the possible datatypes for multiple tables like:</p>
  
  <pre><code>        propDateEvent                   propNumberEvent
  --------------------------          --------------------------
  eventID | propId | value            eventID | propId | value 
  --------|--------|--------          --------|--------|--------
      1   |    2   |  10:00              1    |    1   |  20
  </code></pre>
  
  <p>Somehow I think every solution has its ups and downs. #1 feels like the simplest but least robust. #3 seems like the cleanest solution, but pretty complicated if I wanted to add e.g. a priority for the properties per event.</p>