SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Feb 2001
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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, so I was thinking that when a user went to there chosen section I would have a column on the table called genre and use

    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

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by neil100
    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, so I was thinking that when a user went to there chosen section I would have a column on the table called genre and use

    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.
    I suggest you read the chapter on "Relational Database Design" (Chapter 5) in my MySQL/PHP article. It covers exactly this type of concern. In brief, you should definitely have a single table for all your books, but you should have a separate table for the genres and probably another table for the authors.

    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?
    Each query in MySQL is an atomic operation. Thus, you don't have to worry about someone seeing a table while it's in the middle of being UPDATEd. That said, if your database structure is so complex that it takes several INSERT's or UPDATE's to perform an operation and the database is in an inconsistent state between these queries, then you should lock the affected tables while this group of operations is going on. Your database would have to be either quite complex or quite badly designed for this to be neccessary, however.

    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.
    This is covered in the Advanced SQL chapter of my article.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Babys got backa hard back, that is: The Ultimate CSS Reference


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •