Re: To Separate, or Not to Separate

Thanks to everyone that responded to my question, it was very helpful! I've decided to keep all the fields together rather than split them into two tables. I understand normalization and I've avoided redundancies in my DB. After a user logs in, what I do is load all preferences into session vars so that I don't have to continually call the DB for prefs every time a page is loaded. So, for that reason, I think it would be more convenient to keep all the member fields in one table. Member billing info, such as billing address and credit card info, is already stored into a separate table (which has very limited access with a separate user/password in MySQL). The Member DB I was asking about holds stuff like username, password, e-mail, membership expire date and various preferences. Thanks for all the input! - Monty > From: cparker@swatgear.com (Chris W. Parker) > Newsgroups: php.general > Date: Mon, 23 Feb 2004 15:34:41 -0800 > To: "Monty" <monty3@hotmail.com>, <php-general@lists.php.net> > Subject: RE: [PHP] To Separate, or Not to Separate > > Monty <mailto:monty3@hotmail.com> > on Monday, February 23, 2004 2:35 PM said: > >> I'm wondering if I will see any noticeable speed increase if I were >> to split the 20+ fields that hold preferences (nearly all are >> single-digit 1/0 fields) out from the other fields that hold general >> member data, and put them into their own table? Or maybe there's some >> other good reason for doing this that I'm not aware of? >> >> I'd actually prefer to keep it all together, because it's a lot >> easier to update a single table in MySQL than several if a member >> makes changes to their account or preferences. >> >> What do you think? > > what do *i* think?? well, i'll tell you. > > i'd say you should definitely keep them in the same table. once you move > them to another table you'll need some way of keeping track of each > users corresponding settings and the way to do that is to have some kind > of unique identifying field that is the same between both tables. this > is usually done with an auto-incrementing ID field. > > you will likely not see any performance increase if you split the > tables. in fact you'll probably see a decrase as you'll have to do a > join to get data from two tables at once. > > and just to let you know (in case you don't already): > > 1. don't select the entire table with "SELECT * FROM user_data". instead > you should explicitly list each and every field you want to grab (even > if it's all of them). > > 2. in case you are not meaning to select the entire table but you are > still doing SELECT *, you'd be much better off following tip #1 because > the database won't be doing as much work. > > > > hth, > chris.