StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: Complex queries or tracking/counter fields
text
Body
copied!<p>I'm just thinking about MySQL database design and there are often situations where</p> <ul> <li>A particular action is or is not carried out and consequently data is or is not stored in the database</li> <li>Whether or not a user undertook a particular action is displayed statistically</li> </ul> <p>An example of this would be:</p> <blockquote> <p>A user does or does not fill out a survey. If they do fill out a survey, the data they provide is stored in the database. The total number of users who filled out the survey is displayed.</p> </blockquote> <p>Now, in order to get the number of users who filled out the survey, we could either </p> <ul> <li>create a field of type <code>BOOL</code> which is set to <code>TRUE</code> on suvey completion; we then calculate the number of users who completed the survey using a simple <code>COUNT(*) WHERE field=TRUE</code></li> <li>calculate the number of users who filled out the survey using the data they provided by joining the users and survey results tables and grouping on the user</li> </ul> <p>This isn't a particularly complex example, but there are cases where without the <code>BOOL</code> flag, queries can be become very <strong>complex and expensive</strong>. But the flag is an almost unnecessary addition to the database tables - we use it only for convenience. Also it means we have to ensure that we <code>UPDATE</code> all user flags at the relevant time, as well as storing user data.</p> <p><strong>What would be your approach to this kind of problem?</strong> For smaller applications, i'll usually just write complex queries and cache their results (occasionally using <em>views</em> to make things more manageable). But in larger applications, with potentially many joins, I might be tempted to flag the users with an action field so that reads are simpler and cheaper.</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