SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2001
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mySQL table architecture

    Ok, I am writing a forum in PHP/mySQL that will one day be widely used. I have been creating the mySQL structure for a few days and since I am new to mySQL or any database for that matter, I would like to know if it is a good idea to create two or three tables just for the user profile table. The thing I am worried about is that the profile table (now only one) has 30 columns. Now when someone views a thread, it is going to have to search through this huge database for every user. The question is that there are many columns that will not be used most of the time and I wonder if I select the table, will mySQL load all the columns in the table (even the many that it doesn't need) thus taking up memory and bogging the system or am I safe to go with a one table for profile because I am right in thinking that mySQL will not load those columns of data unless I specifically select data from those columns. Any help/thoughts will be nice.

  2. #2
    SitePoint Evangelist AlexC's Avatar
    Join Date
    Oct 2000
    Location
    Sheffield, UK
    Posts
    437
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, vbulletin uses one table, and that seems stable enough
    Nearly 7 years old!

  3. #3
    SitePoint Member
    Join Date
    Jul 2001
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I guess if vBulletin uses one table for all that stuff, I guess I don't have to worry about it. I have heard that vB uses in total 37 tables, now that is a lot. I have 10-15 right now and I think of new information all the time, however, it is mostly new stuff that I add to other tables.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you are describing - splitting up a table into two tables - is part of the processes of denormalisation. This is where you reconfiger your normalised data schema to improve the efficiency of your database queries.

    I have taken the approach of splitting up user data accross two tables for a web application I am currently developing for which there will be 100,000+ members in the member table. This is for the same reasons you mention - because most queries on a member will only need to access a sub-set of the attributes for a member.

    Is what I did overkill? I don't know. (lol - I had typed "dunno" which is Aussie slang for "I don't know" - so much easier). One approach to data normalisation is to monitor your database performance and benchmark your queries as the database and usage grows and take denormalisation and efficiency measures when they seem necessary. In my case it was just easier for me to pre-empt where I could envisage bottlenecks occuring and split the table at design time.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •