database design-- no one responding :( 1

[joeisbatman]

I've made this post in a few of the databse forums to no avail, I'm hoping someone here can gimme their two cents:

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

 

[link9]

Well, of course, it all depends, but as a general rule, if everything could easily be organized into a single table rather than having many one-to-one relationships to several tables, would normally be put into one table. The reasoning is simple... when you go to retrieve this data, the fewer joins you have, the faster your query is going to perform.

Sometimes, if I have a large demographic database and I have many columns that I, personally, won't be using to perform MY work, then I'll put the "useless" stuff into its own table, with the aforementioned one-to-one relationship to that table, and then I'll keep the columns I use alot in its own table.

However, this does make adding to the database a bit more complicated because when you do an INSERT, you must INSERT into multiple tables w/ the same key, etc...

If it were me, I would probably organize it into one (or few) tables.


paul