database planning

[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

 

[thegman]

If I were you, I'd go with 1 query over 8! And you're right, a primary key search will certainly be faster (a lot)than searching for a word, and as your primary key will be a number, it will go faster still, as databases can find numbers faster than text. I think the only reason you want multiple tables in a fairly simple database is if you want to have one-to-many matches, which is if say you have a database of members of a club, they may have multiple addresses, work, home, holiday, etc. then you would probably want to have a seperate table for addresses and link to the main table of members. Although bear in mind that MySQL cannot do sub-selects in any serious way, so joining up data from multiple table must be done pretty much manually in Perl or whatever, but any speed hit you get from this is pretty small.