One big table or a few small ones?

Anon

I have recently learnt the basics of mysql, ie - creating a table, select queries, using joins etc.

However I have a couple of questions before rushing into designing the database for my site and was hoping I could get some advivce here?

If I have say list of books with title, author, date added, id and a couple of other columns. Would this be best going in one big table or several smaller ones, bearing in mind the following:

I would never actually need to display all the books from the table at once, because the way the site is laid out you select the catergory you are interested in from the index page for instance sci-fi or romance, soi was thinking that when a user went to there chosen section I would have a column on the table called genre and use a where genre = \"romance\", however would this be slower than me simply creating a seperate table for romance, sci-fi etc. There would eventully be about 15,000 rows.

My other question is regarding locking tables, I did not really get this in the manual and the tutorial from sitepoint did not go into it. But I think I may need to lock tables because I want to give people the option to rate books, but am worried that when someone votes and the column with the current rating gets updated, if someone else looks at the table and starts a select query that either it wont update correctly, get corrupted or just crash. Should I be locking the tables or not?

If anyone knows a good tutorial on locking that is simple (I am a rank amatuer at this) then I would be very grateful for there help.

Thanks for any help - Neil

Anon

Hey if you find a tutorial send it my way, I've been doing MySQL for a year now but I'm still game for learning more tricks.

Mike

Anon

Hi Neil,

I'd say it is definitely woth having more than one table, but probably in a different way to what you're thinking. I don't think you want to have a different table for each genre. (What if you want to get all books by a multi-genre author? You'd have to look in several tables.)

What you might consider is a table called genre:
genre_id int primary auto-increment
genre_description text

eg.
101 Science Fiction
102 Romance
103 Horror

...and then use genre_id in your book table. Imagine if you wanted to expand your Sci Fi genre to be called "Science Fiction and Space Tales". You'd only need to change the description in one place, and you'd never need to search through all your tables for long strings "WHERE genre = 'Science Fiction and Space Tales". The power of a relational database means you don't have to get yourself involved with this kind of database-killing text searching.

Now thin about a separate author table complete with author_id. Can you think about Author details that change over time (name, etc) that you don't want to have to chase down in your book table.

Hope this very clumsy intro helps. There are heaps of good sites and books on the priciples of relational database design.

As for locking... Have you thought about maybe keeping all the ratings in a different table so seperate INSERT clauses don't clash?

Cheers,
Duncan.

kparker

Neil,

Asking "One big table or a few small ones?" is simply taking the wrong approach. Instead, you need to ask what best fits the data.

Fortunately, you don't have to reinvent the wheel here, or discover the First Principles. Just search for "normalize" or "normalization" here and you should discover some pointers to good information on getting started.