Re: Re: To Separate, or Not to Separate

blah blah blah semantics aside; what i was saying with 20 records is that you wouldn't necessarily find a performance increase rdbms-side; but you will experience a headache trying to port your current app over to another schema; unless there's a good reason for this - leave it. but if you want extensibility and the ability to effectively retain one-to-many relationships (which his current schema does not provide for) then you should look into segregating the information. innodb also provides for foreign keys and transactions, which myisam does not. ~phillip "Chris W. Parker" <cparker@swatgear.com> wrote in message news:001BD19C96E6E64E8750D72C2EA0ECEE447901@ati-ex-01.ati.local... Phillip Jackson <mailto:tzmguitarist@hotmail.com> on Tuesday, February 24, 2004 11:47 AM said: first of all i'd like to say that my comments below are based upon the way i understand things to be. i could very well be wrong. so someone (phillip) correct me if i'm wrong. > what you describe is called 'normalization'; if you'll always only > have 20 people in a table it may not be worth it for you - though > it's poor practice to continue to design tables in this fashion. come > up with a nice naming convention and design tables that break contact > information such as name, address, etc. away from other information, > such as username and password. that's not normalization. normalization is "the process of organizing data to minimize redundancy"[1]. > breaking out site prefs is a good idea, too becuase you may or may > not need to call these prefs every time you do a page load. the number of columns in a table is probably negligent when it comes to retrieving data from a table, *especially* if you've only got 20 columns. in the first place you should only be selecting the columns you need and not everything (as in "SELECT * FROM table"). > regardless it's good practice to seperate information not pertinent > to the other fields. true but only to a certain degree, and this is also subjective. more important is the kind of data is disparate. also this really only counts when normalization is involved and not in the original posters case since i don't recall him mentioning anything that sounded redundant in his user table. why have four tables separating user preferences, user names, user addresses, and user login/password information if each user is always, only going to have one record in each table? > another thing you should consider is if someone has more than one > address - such as a po box and a home address... would you require > them to choose only one? you're on the right track but this really isn't a good example for normalization. a better example would be if you're going to allow multiple po box addresses, or multiple home addresses. let me make an example of normalization. (a poorly designed table) USERS id (primary key, auto-increment) name home_address_1 home_address_2 home_address_3 home_address_4 home_address_5 home_address_6 po_box_1 po_box_2 po_box_3 po_box_4 po_box_5 po_box_6 now with normalization: USERS id (primary key, auto-increment) name USERS_ADDRESSES id (primary key, auto-increment) u_id (foreign key, users.id) home_address po_box with the first table you could only have up to 6 home addresses and 6 po boxes for one user. in the normalized example you can have a nearly infinite number of both for one user. > if you require a one-to-many relationship then you may want to > break this out; now *that's* normalization. > mysql has built-in checking with innodb tables to reference foreign > keys to do error-checkign for you (ie referencing someone that > doesn't even exist or deleting a recordd that has information > referencing it). > > http://www.phpbuilder.com/columns/barry20000731.php3 i didn't know that, i'll have to look into it. chris. [1] http://www.webopedia.com/TERM/n/normalization.html