Re: Developing a dating service!

All, Although there is a time and resource penalty for having more tables, this is (I personally think) out weighed by the benefits of the extra flexibility that multiple tables provide. This is especially true for "future improvements". Invariably the user, once presented with the solution, needs "adjustments", which may require fields being moved out of the "not quite often used" table into the "often used" table. Repeating data is one of the problems that normalisation solves. If you don't mine a bit of repeating data, then you can improve speed performance, however the tables will take up more space. As a point of reference I was presented with a set of data in one table, which took up 120 MB + 20 MB of indexes. After normalising (breaking up into several tables) and indexing the data was reduced to 12 MB + 8MB of indexes. This had the overall advantage of being able to hold all of the data and indexes in memory; a remarkable speed improvement. Although this is an extreme example it can illustrate the advantages of normalisation. I agree it depends on how much of what you are storing. I'll stop rambling now! David. ----- Original Message ----- From: Web Master <webmaster@unni.com> To: David Newcomb <davidn@vnvi.com> Cc: <sandeep@wde.org>; <php-general@lists.php.net> Sent: Thursday, September 07, 2000 4:38 PM Subject: Re: [PHP] Developing a dating service! > Try to identify most important fields and more commonly used fields and use > them in a single table. More table access, more time you are going to take. > Somebody correct me if I am wrong here, you can have a as much columns as > possible in a single table, so you can stay in a single table access rather > than a join or multiple selects. > When you have a big table make sure, you define indexes properly (based on the > use of the fields). > > > David Newcomb wrote: > > > Sunny, > > > > > I've read about database normalisation and using seperate > > > tables interlinked so that information can be categorised. > > > Should I be using this? Any relevant links / articles? > > > > Yes, normalisation is a must, to reduce data size and increase flexibliity. > > There are loads of books on this subject. > > > > > Can people please offer me advice on possible pitfalls as > > > well as the database design tips which I can use for such a > > > service?? > > > > One thing I wish I had known before starting a database project: > > Give all the fields in all tables a unique name. > > eg create table regested_users { rus_userid, rus_username,rus_passwd } > > create bought_item { bit_itemid, bit_rususerid, ... } > > now when you use: > > select rus_username > > from bought_item > > where rus_userid = bit_rususerid > > and bit_itemid = 24; > > you do not have to alias the tables to get the correct fields > > > > > ANY help would be appreciated!! > > > > Download a program called phpMyAdmin which is a web front-end > > to MySQL - striaght forward and simple to use. > > > > Just a couple of things to thing about. > > > > David.