×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

database planning

database planning

database planning

(OP)
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



 
 

RE: database planning

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close