SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    1 table vs more tables with less fields

    I am planning to create an user table. Data will be: Username, e-mail, password, image, age, ... favourite items, friends... and about 30 more fields.

    I am wondering what is a better choice:
    Create 1 table with all those fields or more tables with less fields?

    Let' take for example friends field. I can put all friends id-s into 1 field as text (friend1:friend2:friend3:friend4) and than use PHP explode function to display all friends or I can create extra table for friends. So is it better to use ...SELECT friends FROM user... or SELECT * FROM friends WHERE userid=3?

    I am assuming that the first case will be better for performance because it needs to pull only 1 row. Where is the edge between 1 table vs more tables?

    Thank you
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Avoid storing multiple items in the same row. You should follow proper normalization practice and use a proper many to many relationship schema using a separate table to relate people to their friends. So in this case more tables with less fields would be your answer.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ernest1a View Post
    I am assuming that the first case will be better for performance because it needs to pull only 1 row.
    not necessarily

    it will require a table scan to find all rows which have a certain value inside a field that contains a comma-delimited list of values

    whereas properly normalized, finding a certain value can utilize an index and therefore will have ~way~ better performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But where is the limit what to use in the same table? I will give you all possible things, please tell me how many of them would be better to put it in extra tables to get an idea:

    -user id
    -username
    -first name
    -second name
    -address
    -country
    -language
    -age
    -gender
    -professional level
    -job position
    -company
    -about me
    -hobbies
    -countries I visited
    -My updates (like twitter)
    -my photos
    -my videos
    -status
    -favourites
    -profile views
    -date of last login
    -date of registered
    -people visited my profile

    Thanks a lot!
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    anything which is plural is suspicious
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Can a user have more than one address?
    Can a user speak more than one language?
    Can a user have more than a single job?
    Can a user visit more than one country?
    Can a user have more than a single twitter update?
    Can a user have more than a single hobby?
    Can a user have more than a single photo?
    Can a user have more than a single video?
    Can a user have more than one favorite?
    Can a user have more than a single person view their profile?

    The answer to all those questions is most likely yes. So it would be smart to use separate tables with foreign keys to the user in which each item belongs.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    agreeing with previous posts, try not to build tables to suit already known queries. You will have more in the future. store them as per rules of NORMALISATION and look up FOREIGN KEYS and INDEXING and CONSTRAINTS and FOREIGN KEYS

    to refer to your previous post, everything between 'hobbies' and 'profile_views' (inclusive), as well as 'people_visted_my_profile' should be in a separate table like this (I think).

    create table hobbies
    ( hobby_id int not null auto_increment
    , user_id int not null
    , hobby varchar(99)
    , CONSTRAINT hobby_user_fk
    foreign key (user_id)
    references users(user_id) on delete cascade
    ) engine=innodb......etc.

    assuming the main table with user_id is called 'users'

    other tables for all those cols, as mentioned, should be of similar format. make sure data type of the parent key is the same as foreign key eg INT or VARCHAR(99) etc.

    oh yeh, read up one 'one-to-one' relationships and 'one-to-many' relationships so you know which type of table to create ~ n-m or m-m.

    hth

    bazz

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    bazz, what is the purpose of the hobby_id auto_increment?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As usual there were errors in my post. Thanks for spotting one

    Code MySQL:
    create table hobbies
    ( hobby varchar(99) not null
    , user_id int not null
    , CONSTRAINT hobby_user_fk
    foreign key (user_id)
    references users(user_id) on delete cascade
    , primary key (hobby,user_id)
    , reverse_ix (user_id,hobby)
    ) engine=innodb......etc.

    Primary key and revere_ix to ensure suitably efficient indexing whatever the query. (I think).

    bazz

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yup, the existence of reverse index means that every query has a covering index

    most many-to-many relationships are like this -- only two columns, and together they form the primary key

    this is a really interesting case, because the hobby column is not an actual "foreign key" to a hobbies table (most relationship tables have two columns where both are foreign keys)

    the hobby column is a sort of conceptual or virtual foreign key, but i'm not sure that the application here requires the potential hobbies to be pre-registered in another table, so having it as a "data" column is fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •