Thread: Table structure

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    26
    Rep Power
    0
    Hello all,

    Iam in early development stages with a site. Iam having a go using PhP and Msql software. Iam wanting to capture quite alot of details from registered users. What is the recommended maximum amount of fields to use in one table. I can quite easily group the specific information into categories and use tables for these groups eg. user details, option selection etc Because I have alot of field info is it standard practice to use seperate tables? Or could I use just one table with alot of fields ? - this would be my ideal method, because I think it would be tidier to manage.
    I can only use one database for this website due to the web host only allowing one database.

    Regards

    Richard
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    45
    Rep Power
    15
    Since having many tables split into organized categories is the best for managment, I'd recommend using multiple tables if you can. However, this is kind of a topic that's a personal choice - what's easiest for you. Like I said, multiple, organized tables are very easy to manage, but can be a pain to get a lot of information if you want it from multiple tables...I'm not making the decision for you...it's personal, ya kno?
  4. #3
  5. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,643
    Rep Power
    4492
    I think this is more of a mysql question, but....

    although it seems like it'd be easier to put everything into one table, i think it's actually better to divide things up into seperate tables. have a main table where you store a user, giving them a unique id, maybe with a auto_increment column. then use that id to link all the other tables back to that user. you could have one table for logins and passwords, another for color preferences, movie preferences, etc...in each table, you only store the unique id to reference back to the user.

    some of the benifits...

    if a lot of people are accessing your pages, instead of querying the same table to every query on your site, the queries will be divided up based on what table the information is in. this will make things faster...

    let's say you have table with id,name,address about a user.
    id=100
    name=john
    address=555 Someplace Way

    now, you want to store all of my movie preferences. if you were using one table, you could have a column movies, where you put all my preferences into a string, stored the string, and cut it all apart when you took it out of the database. this is rather cumbersome and goes against the rules of a relational database...a better idea would be to have a seperate table for movies where you store my id and movie...
    100,Scream
    100,Scream2
    100,Scary Story
    100,Fight Club

    then when you want to get my movies you just use "select * from movies where id=100" or something similar..

    I hope this isn't too confusing and this helps you make your decision. as always, I could be totally wrong and maybe someone else has a better idea. in that case, use my $0.02 to buy something nice...

    ---John Holmes
    ---www.SepodatiCreations.com
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    Perth West Australia
    Posts
    757
    Rep Power
    15
    I agree with John and Polman in general - but it depends a lot on what information you are holding on the users - some info can be held as a comma seperated list in a text field and then split back into an array of info - this is faster if you have a lot of small snippets of info which are recieved in a logical order.

    The biggest overhead for any of your pages will be the database query. Anyway you can keep this to a minimum, can make a noticable difference to your load times, where as the server overhead in crunching your PHP only shows a fraction of a second difference for 'good' and 'bad' coding (with the exception of RegEx).

    Planning your DB before you start is worth every second. Keeping Queries to a minimum - and only extracting the information you need in any one query is my best guess at an answer!

    ------------------
    Simon Wheeler
    FirePages -DHTML/PHP/MySQL

Similar Threads

  1. Replies: 21
    Last Post: June 23rd, 2004, 06:07 PM
  2. League table problem
    By silverduck in forum MySQL Help
    Replies: 8
    Last Post: February 10th, 2004, 12:38 PM
  3. Replies: 0
    Last Post: January 30th, 2004, 02:18 PM
  4. how to keep table on top, not centered??
    By vinyl in forum HTML Programming
    Replies: 2
    Last Post: January 20th, 2004, 06:19 PM
  5. Controlling Multiple table Heights in a Browser
    By AttaBoy in forum HTML Programming
    Replies: 1
    Last Post: December 10th, 2003, 04:24 AM

IMN logo majestic logo threadwatch logo seochat tools logo