Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

cabrinha98
cabrinha98
10,616 Points
Posted by cabrinha98
cabrinha98
10,616 Points

MySQL intelligent Nesting? (multi-level-tables)

Hey guys!

After I managed to finnish a few PhP-Courses I want to stabilize my knowledge and improve myself with a "bigger" project for myself.

At the moment I´m at the planning phase and thinking about a MySQL Schema ... and therefore I`m seeking for a little help from you guys.

My idea is to create a Database for Series for my friends and myself.

Therefore I`d create an Login-Table for all the Login-Data and a Series-Table with Series-ID,Title,Description,etc. ... but as every Series has multiple episodes and seasons I think it would be wiser to store the informations for every Series (episode id,description,title,rating,etc.) in an extra table with an table-id related to the movie/series-database.

So at the end I would have lot more than 3 tables (login_table, film/series database and for every series an own table for that specific series with all the informations I named above).

So I think that is really bad performance wise ... when thinking about 20 Series = 22 Tables! Also when I think about commenting Series/Films it would just make the Server pretty damn slow.

So, what is a better Solution for storing multi-level MySQL-Tables?

Can Arrays be stored in Tables? Or Tables in Tables? I`ve just no clue. What is the best way for getting the "best as possible" performance?

Would love to get some tips/ideas in how to manage such multi-level MySQLs =) Hopefully some can help me, so I could continue my "little" project =P

mfg

2 Answers

Jose Soto
Jose Soto
23,407 Points
Jose Soto
Jose Soto
23,407 Points

Sorry, I added that as a comment, not an answer. Here it is again. Would you mind voting it up if it helped? Thanks.

Hey there. This sounds like a great project to really gain some knowledge on working with databases. Schemas are important to get right early on since it would take much more time to fix a bad setup.

The power behind a relational DBMS like Mysql are the queries, which rely on good table structures. You aren't allowed to store weird values into tables (like other tables, or arrays). Plus, you really wouldn't want to since you will likely want to query your tables for relevant data, and nested tables would screw that all up.

If I were starting a project like yours, this is how I would structure it:

DB table schema

One table would hold the data specific to the series and the other would hold the data specific to each episode. The Episodes table would use a composite primary key (series_id, season_number, episode_number) to avoid duplication.

With this setup, you can create views/joins/any queries that should satisfy your needs and provide proper relationship between the data.

Cheers!

cabrinha98
cabrinha98
10,616 Points
cabrinha98
cabrinha98
10,616 Points

Hey,

thank your for your answer! So in fact I would use 3 tables (login_table, series_table and episode_table) and link them together as it`s needed.

My idea of the MySQL Schema wasnt that far away, thats good to know =P

I really appreciate your help.

mfg