PHP Book System Design Question

[jazzgame]

Hi all,

I am designing a system that uses apache, php, and mysql.
The system works with books. I have a db model that has the following tables:
books- contains book title, author, isbn, description, status
sections - containing section name, description, id, status
chapters - containing the chapter name, description, id, chapterContents, status.

I need proofreaders to be able to proofread anything I update before it goes live(that's what the status field is for).

My question is regarding the chapterContents:
1) Should I break apart the chapterContents into paragraphs allow each paragraph to have a heading, lastUpdated field, lastEditedBy, and lastProofReadBy)

2) Or should I keep the entire chapter in the chapterContents field and store it there as an xml in a very large text type?
The xml could have a title element, an updated attribute beside each paragraph, etc.
Everytime there is a update made, maybe a new xml file is created with the appropriate changes.

I don't want the system to be limited in anyway, some future ideas I have:
-show the user what content has been updated
-pull out the titles for someone on a PDA or cellphone

I have not written one line of code, yet, so any extreme ideas are welcome too!

Which way should I go?
Jazzgame

[mcarman]

If you want to make sure your project is not limited you should review your database normalization rules. That is the key to flexibility and scalability. I see a few potential rules violations but then again I don't know exactly what data you are putting in your fields.

[jazzgame]

thanks mcarman for reading,

I will definitely review my normalizing rules again.

But should I break apart each chapter into paragraphs(very granular) and store the paragraphs individually or should I store the whole thing as xml in a BLOB?

What are the pros and cons of each?

Jazzgame

[mcarman]

Let me ask you, because I am not completely following what you are planning to do, are you storing the entire contents of the book in your db? I was assuming you were just reviewing the books or something.

[jazzgame]

Sorry about that. But no book reviews, the entire book contents is in the database so it is completely searchable and I can have a CMS around the books in the database.

Thanks again your input.
Jazzgame

[mcarman]

Wow, that’s a lot of typing. To be honest I have never dealt with putting that much data, an entire books worth, in to a database. If you are just working with a few books I guess it wont be too bad but if you are going to be dealing with lots of books that is going to be a large database.

Personally what I would do if I were going to work with the entire contents of a book would be to break it up by pages instead of paragraphs. Pages can still be associated with their respective book and chapter but it is easier to find a page then a single paragraph so even though it will be a little more info it will actually be easier to work with maintenance wise. I would then make each page a text file and access it that way. I think that would make it easier to work with as you have an easy to follow reference, the book its self, to use while you are working with the data. Plus your database will remain small and efficient, efficient=fast, and will be much easier to maintain. PHP has lots of good functions for working with text files.

Anyway, that’s my spin on it but there are other ways to go about it. Just remember if you use BLOBs any SORTS or COMPARISONS used on data will be case sensitive.

[jazzgame]

thanks mcarman for your input.

So to re-iterate, keep the database small by creating individual text files outside of the db and have a link to them in the db. Each file will be a page in a chapter in a book.

Question: How how I search the files if they are not in a database? Do I have to open each file individually, if so, how CPU intensive is this operation?

Also, thanks for the tip about the BLOBs being case-sensitive.

Jazzgame

[mcarman]

Hmm… I don’t know what I was thinking; I don’t suppose it would be a problem performance wise to put the data in your DB. I would still organize it by page rather than paragraph though.

To answer your question about searching the text files, yes, you would have to open them to search them. I have never bench marked working with lots of text files but I wouldn’t think it would be much of a performance hit. But I see that you want to allow searching of the entire books content so it may just be easier to stick with the DB.

I was thinking of the text files more of on the admin side. Seems easier to work with if you are going to be working with that much data. I wasn’t sure how you were going to enter the data, scan it in, type it in, etc... so I threw out that idea.

As far as storing the data as XML, I assume you want to use that to format your text for display on the page? If so you can also store the data as regular text and use your own custom tags and format with str_replace(). If you use TEXT instead of BLOB it will not be case sensitive.

Anyway, just tossing out ideas here. Maybe someone else can offer something better. Like I said, I have never worked with large amounts of data like whole books before. I’m sure someone somewhere has so maybe they have a good efficient way of doing it. It sounds like a good challenge, good luck with it.

[jazzgame]

I would still organize it by page rather than paragraph though

That was a great idea, I will definitely do this.

I wasn’t sure how you were going to enter the data, scan it in, type it in, etc... so I threw out that idea.

Two ways, through a WYSIWYG editor and create some sort of word converter into the xml I want.

As far as storing the data as XML, I assume you want to use that to format your text for display on the page?

Yes, and when RDMS's support xml data types (like mysql) I will be able to go into each page and get more specific information out(all depending on how I structure the xml)

If so you can also store the data as regular text and use your own custom tags and format with str_replace(). If you use TEXT instead of BLOB it will not be case sensitive.

I will definitely be using a TEXT field, I don't want to search case sensitive.

I’m sure someone somewhere has so maybe they have a good efficient way of doing it

That would be awesome if they would add to this thread.

It sounds like a good challenge, good luck with it.

Thanks, and it is.

Good luck, mcarman, with your future endeavors as well.

Jazzgame

[WraithGuard]

just want to add, that if you dont already, u may need up to 2 id sub levels in your database.. ie so that you can go in and specify what book to get the section from, then what section and book to get the chapter from.. i think you would need that, there is probably other ways just within php if you have some other identifying id in your database, as i see you have, however u may need to have 2 for the chapters.. or jsut compare the concaternated id's to one you store in there.. hope that didnt confuse u, but u might just want to think of everything that you will possibly need, it will be alot easier to have it in your database to start off then remove it later if you dont need it, good luck with your project, if i think of anything else i will let you know