Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>flat files are obviously a bad idea, because you'd need to implement locking (the db already does that, and there's fewer bugs in that code).</p> <p>relational database design is more of an art rather than a science: you can have</p> <pre><code>CREATE TABLE threads ( tid THREADID , title THREADTITLE , views COUNTER , PRIMARY KEY (tid) ); </code></pre> <p>and it'll be no more and no less "correct" than</p> <pre><code>CREATE TABLE threads ( tid THREADID , title THREADTITLE , PRIMARY KEY (tid) ); CREATE TABLE views ( tid THREADID , views COUNTER , PRIMARY KEY (tid) , FOREIGN KEY (tid) REFERENCES threads ); </code></pre> <p>so it's really up to you.</p> <p>i'd say: go with the simplest thing first, make it more complicated if you find it's necessary (e. g. for performance reasons). IOW: put the <code>views COUNTER</code> attribute in <code>threads</code>. if it turns out the trafic is hurting performance (too many updates on the <code>threads.views</code> attribute mean the dbms must shuffle around otherwise immutable data in the other attributes), you can always split the table up in two, and replace it with a view that joins them. voila, immutable (or rarely changing) data separated from the volatile data, interface remains the same.</p> <p>of course, go with PostgreSQL. the above shown code is valid in that dbms, just add these:</p> <pre><code>CREATE DOMAIN threadid AS INT NOT NULL; CREATE DOMAIN threadtitle AS TEXT NOT NULL CHECK (LENGTH(VALUE) &gt; 0); CREATE DOMAIN counter AS INT NOT NULL CHECK (VALUE &gt; 0); </code></pre> <p><strong>edit</strong> to refute the comment by OMG Ponies: of course it's safe.</p> <pre><code>UPDATE threads SET views = views + 1 WHERE tid = X </code></pre> <p>either succeeds or bails out.</p> <p><strong>edit 2</strong> to add consideration for the voting aspect</p> <p>let's say the spec is: a user may vote a thread up (+1) or down (-1), the sum of his or her votes on a given thread may not exceed |1|, and the history is irrelevant. iow a user may vote a thread up, then down to reset their vote to "no vote", then down again to "voted down", etc.</p> <pre><code>CREATE DOMAIN vote AS INT NOT NULL CHECK (VALUE BETWEEN -1 AND 1); CREATE TABLE votes ( tid THREADID , uid USERID , vote VOTE , PRIMARY KEY (tid, uid) ); </code></pre> <p>in MySQL, you could</p> <pre><code>INSERT INTO votes ( tid , uid , vote ) VALUES ( X , Y , Z -- +1 or -1 ) ON DUPLICATE KEY UPDATE vote = vote + Z </code></pre> <p>alas, PostgreSQL doesn't (yet) have such functionality built in, so you'd need to use the <a href="https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql">idiomatic user-level implementation</a> </p>
    singulars
    1. This table or related slice is empty.
    1. PODatabase schema for forum thread votes/views, and strategy for incrementing and displaying # of views
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USjust somebody
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for that long and comprehensive answer, I'm going to try both your and OMGPonie's suggestions and see how it goes.
      singulars
      1. PO
      1. USmeder omuraliev
    2. COSo every unique vote would go into the `votes` table if I decide to it separate from the `threads` table.. that means on every page view I would need to SELECT the total number of rows pertaining to `thread_id` from the `votes` table, add them up? And sorry if I missed this, but should I be updating the view on every page view, or do something like use the # of replies and ajax post to the db so it only updates one table at a time?
      singulars
      1. PO
      1. USmeder omuraliev
    3. COUse a precomputed aggregate if you find the SUM() on every page view too slow. I couldn't grok your closing question, sorry.
      singulars
      1. PO
      1. USjust somebody
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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