SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Large Data Storage Issue

    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

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

    Code:
    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?

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    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

  4. #4
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.


Bookmarks

Posting Permissions

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