Database planning 1

[joeisbatman]

I'm making a website, and I'm fairly new to databases. My friend and I are butting heads on which is the best way to design my database. My database has members which are kept track in the database using a Unique ID#. Each "member" has a bunch of columns of data associated with it like "name, phone #, description, hobbies etc". WOuld it be better to make One or few LARGE tables or lots of small ones. I suspect large ones might be good (please let me know if I'm on the right track). Since the only thing I'm adding to the DB ever is MORE members, when I want to export my data, I can do ONE query, find that person's stuff, store it into a variable and deal with it as I please. My friend would have me make LOTS of little tables, and do MULTIPLE queries (about 8 or so vs just one) on those tables to find my appropriate data. My queries will be VERY SIMPLE (just find records via the id # is the only query i'll be using). My friend's arguement for his way is that "I'm moving a lot of data around when its searching for the resultant record". My arguement is "I'm only searching thru X amount of members REGARDLESS of how many COLUMNS i have. Which would mean it should be better to do LESS searches on a finite amount of members. Am I correct on the assumption that a query speed is not related to amount of columns, only rows (because I'm searching for ID # which is a column). Essentially his idea is to reduce the amt of columns I have, but making many tables with the same amt of rows (members) but smaller amounts of columns.

One last thing, does anyone know how mySQL searches an autonumber primary key vs a search on say a "word"? I assume the primary key search is much faster because its a sorted list of data... (binary search tree?)

Thanks guys for all your help.
--Joe

 

[ToeShot]

I would like to share with you my thought process on database planning as for the number of tables an the data contained in them.
Almost every database has what I concider to be a standard table this includes all the pertinent client information such as address, phone, email, etc. That is what I call my ONE table because there will always be one uniqe client and never be duplicated. now if that is all the information I wanted or needed then yes I would run with one table, but this is rarely the case. So now I think about the other information what is it? Will it be repetitive? How can I optimize it? I don't know all of the fields you have but I personally don't like to keep a lot of Fields in my tables aminly because of managability and performance. for example say I have 35 fields and I put them in one table now I index 13 of those fields this is a large perfomance hit becuase when I access that table one marks all my indexes two sorts to indexes three returns the data. This is not bad on when you are looking for very specific piece of data as it access all of the Indexed fields to locate that record but when you are looking for large amount of records this starts to take longer because it access all of the indexes. Now lets say that you don't need of of the 35 fileds just 3 well the database is still going to go through all of the fields to get the 3 you need which is another performance hit. But if I was to have say 7 tables with 5 fields each then I could access just that one table that I need the record from with only one index making my query and data retrival faster because there would be less fields and most likely less records. I may need to do a join between to tables to get other information but this may still be faster to access even if it is two tables with small amounts of information, than trying to access one large table with mass amount of information. Also lets say that you have a field or two that you are typing in the same information lets say your hobby field. We will say that you have 15 records with sports as a hobby and another 24 with gardening 5 computers etc. The first possible problem could be is that Sports and sports and SPORTS in some databases are not the same because of case sensitivity. So when you run a query counting the records of hobbies that have sports you will not get an accurate count. But if I had a hobby table Then I could put sports in once and relate my primary record to that hobby this way i would always get and accurate query because it would be spelled only once and used as a lookup( hope that makes sense). This also decreases your table sizesince you went from 15 records in sports and 24 in gardening and 5 in computers to one in each a total of three records thus increasing performance. Obviously if you have a large amount of tables you will find performance issuesas your data may be to segregated to perform optimal data retrieval, but with a well thought out database design and some thorough testing you can come up with a good database for your needs. Now that was my thought process on how I design a database. and I will agree with your friend that One table is not I deal but you may not need 7 or 8 either. sit down with your friend and decide how your data will be used and stored and retrieved.
As for the MySql question I am no to sure about what you are asking but the Primary key is an Indexed filed of Long Integer type data unsigned I believe. Meaning that it will not use negative numbers.

I Hope This Helps also I am going to assume that you are going to use MySql as your backend database server there are some things you might want to keep in mind. MySql is not full SQl ANSI compliant as of yet. The Alpha 4.0 that is released right now is close but still has some issues. It also not support the Table joins which I think is suppose to be in the 4.0. There is still not suport for nested queries. No support for transactions is in 4.0. these are just some. On the Plus side it is one of the fastest database I have seen for free it is very stable as for reliability everyone has probally had a bad experience with some databse or another and I have not personally had any dataloss with MySql but I had heard of other people who have.

I suggest doing some research full force on what you are doing.

Dave

 

[joeisbatman]

Dave thanks for your post it was very informative!

--joe