Sign in to follow this  
Thevenin

Various MySQL Questions.

Recommended Posts

I wasn't able to fix my Mutex/Semaphore code, and so I quickly ported all the data over to MySQL and integrated it into the server. The server is online right now, and working, but various parts of the website our offline until I can get answers to these questions.
  • Will locking a table prevent all processes/threads/etc from accessing it? Or just the calling thread? If its just the calling thread, is there anyway to prevent other proceses/threads/etec from accessing it?
  • Is it a bad idea to use separate tables for each topic started in a forum (Each reply would be a record in that table)? Its the only simple design I can think of that works out. In a year or two, there may be thausands of tables...
  • Do {insert plural name of difficult monster} drop {insert plural name of rare item}? How often do they drop it? Think I can kill that monster? Whats it like being a gamemaster? Thanks [smile]

    Share this post


    Link to post
    Share on other sites
    Quote:
    Original post by Thevenin
  • Is it a bad idea to use separate tables for each topic started in a forum (Each reply would be a record in that table)? Its the only simple design I can think of that works out. In a year or two, there may be thausands of tables...


  • Well, I don't know how to say this nicely... Just don't do that. It is an affront; you are just not supposed to create new tables as part of the standard operation of a database.

    The correct way is to have one table for topics, and another for replies with a foreign key reference to the topic they belong to. If this doesn't sound familiar, I ***strongly*** recommend reading an introductory textbook or three about relational databases before starting anything resembling a real-world application.

    Share this post


    Link to post
    Share on other sites
    Dynamic creation of permanent tables == bad.

    The above poster posted the correct method.

    Any two things which have similar types of data are ROWS of a common table, not different tables with identical schema.

    Any groupings that need to be maintained are rows which have a common value for a certain column. Such as all accounts which have active = true are active accounts, and all posts which have owner = "xai" (or a foreign key to Xai's record in the user table) are Xai's Posts.

    So, it would follow that posts which have thread = SOME_THREAD_ID are the posts in that thread. And you will almost definately need to store either the "original post time" or a "post order" to preserve the order they we're posted in - for display.

    Share this post


    Link to post
    Share on other sites
    Quote:
    Original post by Thevenin
  • Will locking a table prevent all processes/threads/etc from accessing it?


  • Yes. In fact, it won't stop the calling thread from accessing it, if you do it through the same connection (if the same thread uses another connection, you will end up with deadlock).

    Normally table locking happens transparently during an update, so you won't notice it. Explicitly locking tables is a techinique which should be used with caution.

    Quote:

  • Is it a bad idea to use separate tables for each topic started in a forum (Each reply would be a record in that table)?


  • Yes. Use a table for topics and another for posts, and put in a FK in the posts table.

    Quote:

  • Do grues drop magic amulets? How often do they drop it? Think I can kill that monster? Whats it like being a gamemaster?


  • Does a bear sh...

    Oh never mind

    Mark

    Share this post


    Link to post
    Share on other sites
    Quote:
    Original post by Sharlin
    The correct way is to have one table for topics, and another for replies with a foreign key reference to the topic they belong to. If this doesn't sound familiar, I ***strongly*** recommend reading an introductory textbook or three about relational databases before starting anything resembling a real-world application.


    I've never* read a programming book before, and likey never will. Why spend some $10-$70 when there is an abundance of tutorials on the internet?

    I took your guys' advice though, I'm not going to use separate tables. Instead, I noticed that I could use AUTO INCREMENT to keep track of the Position because even though a post may start at Position=563, the fact that MySQL will sort it for me means all I do is make the first reply of a Distinct(ThreadID) the main post, and the rest replies.

    Its simple, its genius, and its (sufficiently) high-performance. [smile]

    The more I learn about MySQL, the less I regret my hastily decision to use it. [grin]


    Share this post


    Link to post
    Share on other sites

    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

    Sign in to follow this