Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006

    Design help - how to split up large user table?


    I'm developing a table for visitors to our site which is getting somewhat monsterous. I'm a fairly novice database table design and I could use some advice.

    Our visitors table initially started out small...

    * user_id
    * name
    * login
    * password

    But then we wanted to record their contact information...

    * address
    * city
    * state
    * zip
    * phone
    * email

    That's all happy. Next we wanted to store how they wanted to be contacted:

    * contact_via_email
    * contact_via_phone
    * contact_via_alternate_phone
    * contact_via_mail

    Of course, next we want to see how they found our site..

    * referred by a friend
    * tv
    * radio
    * etc..

    Then optional personal info...

    * age
    * job
    * household size
    * renter/buyer
    * etc...

    If I store all of the fields into a single database table, the table could get pretty huge. So... my question is... if you have a single thing in your database that contains potentially hundreds of attributes, how do you handle it?

    - Bret

  2. #2
    Join Date
    Jun 2006
    I in no way pretend to be an expert in normalization and you should definitely normalize as much as is reasonable. Sounds like you're fairly new at it so I suggest as a starting point. maybe shoot for level 2.

    You'll probably have plenty of time though to get it right. I started a tiny database to track 10 stats for 15 people on a monthly basis. I was assured that that's all we were tracking so I just threw it together. It's now 18 stats on 1300 people being updated daily. 4 months later I'm starting to lag if I get many people connecting. 1 person connecting I can still find just about anything in less than 1 sec using a desktop computer.

    Start by seperating out the things that lots of people will be using.
    user_ID, Login, Password
    Not sure if Login is unique? If it is, then you could use it as user_ID?

    Next you'll probably want to get contact information out of the way. Address in 1 table (you probably won't have 100's of people at the same address but you can normalize for that if you want also). tie it with user_id as a foreign key. Don't forget to create a primary key!

    optional data you probably won't use much? Unless you're selling it as a primary business.

    Basically i'd just start by seperating out into 3 tables. Play with it. See if you're entering the same information repeatedly. then sort it out again.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by clone45
    ... if you have a single thing in your database that contains potentially hundreds of attributes, how do you handle it?
    in a single table

    there's no problem in having a table with lots of columns

    all database engines are capable of handling this

    stop worrying | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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