Large Data Storage Issue

[rashid100]

Hi,

I am making the website where I have to store a large amount of data like plenty of books using PHP MYSQL. Could any body tell me that what the best way to save books in a database so then what data type we have to use OR should be save in txt file format ?


Thanks

[Cups]

Hmmm .... You will have to explain a bit more I think.

You want to store a book in a database.

Have you considered that you could store the meta data about the book in the database, and the text of the book itself on the file system?

That way you can sort and filter the "books" by ordering their meta data in SQL queries, and the table then holds a link to the actual book in the file system.

books
=====
title | Mysql cookbook
isbn | 4923847592385
pages | 120
authors | A Writer
url | /books/mysqlcb.txt
file_type | text
date_published | 2001-01-01
date_added | 2012-11-28

Is that what you mean?

ps Things to consider:

What if the books contain images?
How will you store these images, how will you make them appear in the text?
How will you handle multiple authors?

[Lemon Juice]

You might also store books in a LONGTEXT column in the database but bear in mind one implication of that. Suppose you have a single table holding books with columns to describe the book (what Cups showed above: title, isbn, etc.) and among them you have the content LONGTEXT column. As the amount of book data grows your SQL searches may become very slow. For example, you want to search by partial title like

SELECT * FROM books WHERE title LIKE '%80 days%';

If you have 10000 books then the database has to do full table scan of all book data. Suppose each book takes up 500KB, which means the database has to go through 5000000KB = 5000MB = 5GB of data just to find a list of books by title. Your only solution in that case is to index all columns that you will be searching and restrict your WHERE clauses so that those indexes will be used - and it may not be practical.

To resolve the issue you have to store the LONGTEXT column elsewhere - either in a separate file like Cups suggested - or in a separate table. Each solution has its vices and virtues.

Advantages of db:
- you can search the content of the books easily with mysql SELECT ... LIKE statements. This is not very powerful, though, but if a simple text search is all you need then you can consider this.
- you maintain data integrity as you can link the tables with a foreign key
- your db backups contain all book data (not just metadata)

Advantages of files:
- they are easy to offer for download without having to write any PHP scripts or engaging DB - just provide a link to the file
- easy to upload/download via FTP

[rashid100]

Thanks to all for your replies well CUP as per your advice Could you plz further advice me for all that issues because If I use the file system then word format file or PDF would be consider where you can put along with images what do you think which way is better. I don't have experience on this type of project but one Issue is again here how I can search on specific words or authors. so It means I have to give file related all information into the database?

[Cups]

It might help if you described a little more about why you are doing this.

Are you just collecting up some available mysql/php programming books which are available as pdf or word docs for you own use, or is this to be part of an offering from a website?

This searching you are talking about, are you likely to search for something like "arrays" or function names such as "array_key_exists", or do you mean search so that you can find the book with a title which is LIKE "%mysql%".

There is a lot to take into consideration here, and we will need to know more about why you are doing it, and how you think it will be used when finished.

There is no need to give away any secrets

re images in pdf and word docs -- you don't need to worry about that, the images are self contained within those formats.

I asked the question because your original post (OP) mentions .txt format, which suggests images are independent of the text.