If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > large column # for a table?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-14-06, 04:24
cfemocha cfemocha is offline
Registered User
 
Join Date: Aug 2005
Posts: 11
large column # for a table?

Im trying to create a table that will store user info (e.g. username, password, personal profile, expertise, interests etc....). I then ended up with about 30 columns for a table.

I don't know if this is a good database design or would it slow down the searching speed. Should I split the table into smaller columns ?

Any help is appreciated.
Reply With Quote
  #2 (permalink)  
Old 08-14-06, 12:19
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
if each column value pertains to only the user, then no, don't split the user table

if any of the columns contains multiple values, e.g. "interests", and you want the individual values to be searchable (instread of the whole thing as a single string which contains text/narrative), then yes, split that off
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 08-23-06, 05:56
cfemocha cfemocha is offline
Registered User
 
Join Date: Aug 2005
Posts: 11
thank you for your reply.
Im planning on splitting the 'experise' column into a separate table. I have roughly 50+ expertise (e.g. asp, php, html etc..) for users to choose from.

Should I build the table with 50+ columns and record the user expertise one per row, or should I build the table with only 2 main columnes (i.e. user id, expertise) which will split user expertise into several rows.

My concern is with the database memory and searching speed once I have more users.
Let's say if I have 50 columns per row and users only know three types of expertise then would it be better off to split the user into three different rows instead of storing data in the same row with 47 redundant columns?

any help is appreciated.
Reply With Quote
  #4 (permalink)  
Old 08-23-06, 07:02
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
do not make a column for each expertise

instead, make the expertise part of the key of a table, and store one row per user per expertise
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On