Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your design looks basically correct.</p> <p>This is a classic <strong>"normalised"</strong> data structure - exactly the kind of shape that relational databases are built for. If you don't know about normal forms, but came up with this structure, then you clearly have a natural understanding of how relational databases work.</p> <p><a href="http://en.wikipedia.org/wiki/Database_normalization#Normal_forms" rel="noreferrer">http://en.wikipedia.org/wiki/Database_normalization#Normal_forms</a></p> <p>In order for PHP to avoid traversing the whole table, you should ensure that you <strong>issue a SQL statement that selects only the records you're looking for</strong>. E.g.</p> <pre><code>SELECT * FROM posts WHERE ThreadId = ? ORDER BY Date </code></pre> <p>Your worry about the database having to traverse the whole table is fair and reasonable, though you can avoid this - this is a classic relational database problem that was solved when they first appeared as commercial products more than 30 years ago.</p> <p>You can <strong>create an INDEX on posts</strong> that supports the SQL you are running. In this case something along the lines of:</p> <pre><code>CREATE INDEX postThreadsIndex ON posts ( ThreadId, Date ) </code></pre> <p>This index allows your database engine to <strong>find the records you're selecting very quickly, without having to read the whole table</strong>. If you want to know how, read up on b-tree indexes.</p> <p><a href="http://en.wikipedia.org/wiki/B-tree" rel="noreferrer">http://en.wikipedia.org/wiki/B-tree</a></p> <p>As I said earlier in the answer, this is exactly the kind of thing that relational databases were built for, and your design is solid and appropriate.</p> <p><strong>Do not consider any alternatives - you got it right first time!</strong></p> <p>But, for completion's sake - let's look at your suggested alternative.</p> <p>You suggest splitting the Post table by User - this would mean the following:</p> <ul> <li>User "UserA" creates a thread - his initial post is stored in posts_UserA </li> <li>User "UserB" responds to the post - his post is storied in posts_UserB</li> <li>User "UserC" responds to the post - her post is storied in posts_UserC</li> </ul> <p>In order to retrieve the full thread you now need to look in posts_UserA, posts_UserB and posts_UserC.</p> <p>If these were the only three users, then you would need to look through all the data in these threee tables in order to find all the posts, and this would be equal to the number of records that <em>would</em> have been in the table posts in your original design.</p> <p>You gain nothing.</p> <p>If you had 1000 other users, you would have to also look in these other 1000 tables in order to find that they didn't have any records.</p> <p>You still gain nothing.</p> <p>You could add added another table to store which users had commented on which posts - and therefore which tables to look in, but now the solution starts to get complicated.</p> <p>You <em>could</em> split the Post table by Thread - this would mean that all the posts where in a table based on the thread that they were made on. This would probably be pretty good for selecting the posts on a single thread, but it would be awful for: - selecting all the posts made by a given user. - finding the most recent post regardless of thread. - finding all the posts made on a particular date. - anything else that did not involve a particular thread.</p> <p>Basically, the alternative that you suggest <em>may</em> be more efficient for a very particular query, but it will almost always be extremely complicated for any other query.</p> <p><strong>The original design is simpler for all queries and can be made to perform well by adding indexes.</strong></p> <p>If you ever get to the point where your SQL performance is too slow because of the volume of data, then you can look at table partitioning, which does what you describe in an invisible way. But to be honest you're unlikely to ever need it unless your site is extremely popular - and if that's the case then you'll probably have the cash to invest in a fundamentals of relational databases course at that point anyway...</p>
    singulars
    1. This table or related slice is empty.
    1. POAn efficient database design for a simple forum using php and mysql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRob Baillie
    1. USRob Baillie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAn efficient database design for a simple forum using php and mysql
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COeven if i did create indices for posts,threadid it would still be inefficent(internally) as the php server would have to traverse the entire table to retrieve records for a paticular threadid, hence I came up with the second design.what are your thoughts on the second database design, not the first one?
      singulars
      1. PO
      1. USjust_a_coder
    2. CONo, PHP should not need to traverse the whole table as you'll select the records you want using SQL. E.g. SELECT * FROM posts WHERE threadId = ?
      singulars
      1. PO
      1. USRob Baillie
    3. COsorry, sql will have to traverse the entire table right to find all entries with a specific threadid.How do think the following works internally?By traversing the entire table right? Even though SELECT * FROM posts WHERE threadId = ? , would do what i want it to,but it would be slower than the second database design right?
      singulars
      1. PO
      1. USjust_a_coder
 

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