StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It looks like <code>userid</code> represents the message creator, and the collection of <code>contactid</code>'s respresents the set of users who will be able to see the message.</p> <p>I would suggest splitting <code>feed</code> into two tables, one with messages and another with a many-to-many relationship of <code>messageid</code> to <code>contactid</code> rather than including the column containing a list of <code>contactid</code>s. This way you will be able to join the two tables to create a query to retrieve messages viewable by a particular user using a simple <code>=</code> rather than a full text search.</p> <p>Here's an example: </p> <pre><code>messages +-----------+--------+------------------+----------------+ | messageid | userid | subject | message | +-----------+--------+------------------+----------------+ | 40 | 67 | Another test | test test test | | 39 | 67 | Here's a test | test test test | +-----------+--------+------------------+----------------+ message_contacts +-----------+-----------+ | messageid | contactid | +-----------+-----------+ | 40 | 63 | | 40 | 66 | | 40 | 65 | | 39 | 63 | +-----------+-----------+ </code></pre> <pre class="lang-sql prettyprint-override"><code>SELECT messages.* FROM messages INNER JOIN message_contacts ON messages.messageid = message_contacts.messageid WHERE message_contacts.contactid = 63 </code></pre> <p>Storing your data this way can help you avoid other problems as well. You can read more about that in one of my favorite answers: <a href="https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad">Is storing a delimited list in a database column really that bad?</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload