Results 1 to 8 of 8
  1. #1

    MySQL database design question

    Hello,

    I am having a dilemma over a new website I am building and the best MySQL database design to implement and achieve best performance.

    Let me explain the purpose and my intended structure first so that you can recommend me better options.

    I am creating a table which would contain about 3000 different TV Shows, and each TV show has many episodes under it(lets assume 50). Now each of these episodes would have an episode description, airing date, episode synopsis, and over a 100 links to watch it, which I would need to store in the database.
    Now my question is, which would be the most efficient way for me to link these episodes and these 100 links so that I can query them in the best possible way?

    Right now I am thinking I would just create a new table which would store all the links for all the episodes of all the shows and would be identified uniquely by a numeric ID and I would create a text column(`links`) for each episode storing these unique ID's separated by a comma which I would later fetch and use according to my needs. Would this be the best way to implement this?

    The problem I think would be the size of the table containing the links as it would have over ~10 million records only identified by a auto incrementing ID and fetching or sorting through this table would be very slow and resource intensive.

    Is there a better way to achieve my objective? Would I be better off dividing these links in such a way that I would have a new table for every 1million links(thereby having the first million in table1, second million in table2 etc?) and therefore having 10 different tables just to store the links? Or should I just opt for some other database management system?

    Looking forward to your constructive feedback, please feel free to ask any questions which will help you help me better

    Thanks.

  2. #2
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,382
    Table shows:
    ID | Name

    Table episodes:
    ID | show_ID | Description | Date | ETC

    Table links:
    ID | episode_ID | link

    MySQL will have absolutely no problem dealing with 10 million rows, so long as it's tuned properly and you've got good indexes set up.

  3. #3
    Thanks for the answer Kevin, so a query like
    Code:
    SELECT link FROM links WHERE id=9876567
    would not take much time at all? Also since I am querying the table through the ID it would be best for me to set the ID column as the primary key which by default also indexes it right?

    The server I would be using has 2x128GB SSD drives and 16GB of RAM coupled with a i7-2600k processor, would this machine be able to perform the above select operations in split second intervals?

    Thanks again!

  4. #4
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,382
    In the links table, the ID column will be the unique identifier for the row and the episode_ID column will be the one you query to get all of the links for a particular episode.

    SELECT `link` FROM `links` WHERE `episode_id`=987654; where 987654 is the ID of the particular episode you want to get links for. Then just loop through the results to display the list of links.

    Combining all of the links for a particular episode into one value is not good practice. What if you need to remove one of those links or edit it? Much easier to have each link be in its own row (and proper database design to boot)

  5. #5
    The method you mentioned would require me to have two more columns `season` and `showid`(unique identifier for each tv show) in the links table as sorting by the episode ID alone would be ambiguous.
    What I planned to do was to have a column `links` in the episodes table, which would store the id of each link in a single text column, eg:
    ShowID | episode | season | desc | links |
    1---------| 1---------| 1--------| abc---| 278,299,300,1003|
    So when I want to fetch all the links for a particular episode of a particular show, I would first fetch the data from links then parse them through php and then again query the links table for the above link id's that I got.

    The difference is that in the first case I have to sort the result depending on three different values( showid, season and episode) whereas in the second I would instead just fetch all the required links by matching them with the data obtained from the episodes table(the ID alone) i.e

    SELECT `link` FROM `links` WHERE ID=278;
    whereas in the second case the query would be more like

    SELECT `link` FROM `links` WHERE showid = 1 AND season = 1 AND episode = 1;
    So which method would be more efficient?
    Thanks for being so patient and answering my queries

  6. #6
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,382
    I'm afraid I don't understand your question...

    Start page: Query shows table. Get list of shows. User picks show, Futurama (ID 1024).

    show.php?id=1024: Query episodes table. Get list of episodes for this show_ID (1024). User picks Episode 3x08 (ID 20481024)

    episode.php?id=20481024: Query links table. Get all links for this episode_ID (20481024) and show them to the user.

    Extremely efficient. Storing multiple values separated by | or , or any other character is just poor design.

  7. #7
    Ah there is the problem, I never thought of having a unique ID for each episode. That would make things much more simpler and make queries much more shorter!

    Thanks for your help, it surely made things much clearer!

  8. #8
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,382
    Happy to help. Just post back here if you have any other questions and I'll do my best to help with them.

Similar Threads

  1. MySQL Database question
    By mad4it in forum Programming Discussion
    Replies: 4
    Last Post: 10-22-2008, 07:07 PM
  2. mySQL database question..
    By qodesigns in forum Hosting Security and Technology
    Replies: 8
    Last Post: 02-15-2008, 01:25 AM
  3. Mysql database Question
    By killapix in forum Programming Discussion
    Replies: 3
    Last Post: 09-30-2006, 07:41 AM
  4. mysql database question
    By jason216 in forum Programming Discussion
    Replies: 5
    Last Post: 08-28-2004, 02:12 PM
  5. Question about mysql database
    By MohamedAnis in forum Hosting Security and Technology
    Replies: 1
    Last Post: 02-01-2004, 12:59 PM

Posting Permissions

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