StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In a comment you basically state that an established friendship (as opposed to a request) is always symmetric in your setup. In that case, you have basically two options: you can either store it in two rows, or select it by matching either column. The former will yield simpler queries, but the latter will ensure that the symmetry is inherent in the database structure, and will avoid storing duplicate data as well. So I'd go for the latter, i.e. some form of <code>WHERE (User1 = XX or User2 = XX )</code>. The query might well take twice as long as a query for just one column would take <em>on that same number of rows</em>, but as the number of rows is only half that of the other storage scheme, the net effect in terms of performance should be negligible.</p> <p>Whether you want a separate table for requests or established friendships depends on how similar those two are, both in terms of associated data and of the control flow in your application. So for example, if you want to present a single list to a user which shows both his established friendships and his pending requests, perhaps with different colors or whatever, but in the same list, then having a single table in the database would be more appropiate. If, on the other hand, you mostly treat requests and friendships separately, then having two tables would come more natural. And if, at some time, you decide that a freindship needs attributes like <code>share_calendar</code> whereas a request needs attributes like <code>confirmation_key</code> or whatever, then you'll be better off with different tables.</p> <p>If you decide to make this a single table, I'd suggest more describtive values for the enum, like calling the column <code>status</code> and the values <code>requested</code> and <code>established</code>. I, for one, would at first glance interpret a value of <code>request = 1</code> as ”this is a request only, not an established freindship”, exactly the opposite of the meaning you associate. This ambiguity can lead to errors when different people need to maintain the code. And in several years you'll be enough of a different person from who you are now that even you yourself might misinterpret your old code. So be descriptive there.</p> <p>One more note: you may always use views to tweak the way your database appears to your queries. For example, you can create a view</p> <pre><code>CREATE VIEW SymmetricEstablishedFriends AS SELECT User1 AS Me, User2 AS Friend, Time FROM Friends WHERE Status = 'established' UNION SELECT User2 AS Me, User1 AS Friend, Time FROM Friends WHERE Status = 'established' </code></pre> <p>This will restrict the data to established friendships only, and will take care of symmetrizing things for you. Using such views in your queries, you can avoid having to deal with all the details of the table structure in every query. And if you ever change those details, there will be less places to change.</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