MySql efficiency question

[AegisSailor]

Greetings friends and neighbors!

Knowing very little about proper database design, I thought I would ask you experts in the crowd.

Suppose - just suppose - I were running a fan fiction site where hundreds of authors were to post their stories, anywhere from 3000 to 150,000 words in length, broken up into however many chapters the author decides.

In terms of efficient database operation, what is the best way to store these chapters, given that there could be hundreds of different stories of varying lenght?

The obvious solution is to create a table for all stories that has, say, 75 chapter fields. This seems wasteful because what if a certain story has only 20 chapters? Or One? And how does a table with that many LARGE fields effect the database?

The only other option I can think of is to have a table for all chapter 1's, all chapter 2's, all chapter 3's, and so on. This seems counter-intuitive to me, but perhaps that is easier on the database engine?

It's important to note that I would like the site to support many simultaneous users.

I appreciate your help! My database experiance is limited to MS Access, so you can see why I'm worried about a poorly performing database!

Thanks again,

Roger

[page1ink.]

you could set up two different tables: one for stories and one for chapters

your 'stories' table would look like this:
'index', 'title', 'author', 'id' (whatever other fields you need)

and your chapters table:
'index', 'title', 'parent_id' (etc)

the 'parent_id' field would contain the 'id' of the stories table.

does that sound like what you're looking for?

[AegisSailor]

Wow, I can't believe I didn't think of that!

Anyway, thanks for the great advice. I guess everybody needs a little poke now an then to get their head out of the dark place :)

[page1ink.]

haha, it happens to all of us =D

[simonP]

Hi,

I would like to make my MySQL database more efficiant, i found this

http://www.fixya.com/support/t883221-mysql_efficiency

The advices seem good, but i'm not sure how to implement them.

Thanks Simon.

[charlesgan]

if i have to choose, i will spend money on getting a powerful server, rather than spending time on scripts tweeking (as there are limit to this).

dedicated database server will able to handle millions of query per sec.
how about sun java.. etc. :D