Michael, here's what I can tell you - Some people will advise you against storing documents in tables - claim it makes the data harder to serve up, etc. I agree it's quite simple to set up a folder structure with your files on the filesystem and point Apache or IIS to it while tracking metadata in MySQL, but there may well be other destinations/uses for your documents where actually having them in BLOBs would be quite handy.
There's no technical reason you can't do it, obviously, and I worked for several years at a midsize newspaper where we stored literally everything in gigantic Sybase databases. Story, ad, page layouts, postscript graphics files, etc. Everything. And by and large it worked quite well. Nearly 1TB by the time I left, and a colleague at another newspaper had near 3TB. A big plus was the centralized workflow and tracking it allowed, but that will depend largely on the quality of the application software you have. At any rate - based on my experience with the Sybase system I managed, I would advise you to consider this when designing your database: instead of having one gigantic table to store every document, try to design a system that allows for splitting the data across multiple identical tables. You could do this with the MERGE engine in MySQL; that has MyISAM tables underneath, with a view of sorts that presents all the underlying tables as one. You could also do it by having multiple "DocumentTable001" tables structured identically, with another table to track the document tables as well as the current insert path. This is obviously more complex but doable. MyISAM is not transactional; InnoDB is, but doesn't offer MERGE. InnoDB can be configured to store one file per table in the latest versions of MySQL, and I'd recommend you go that route. Having the data split across multiple table files (in MyISAM or InnoDB) will allow you to check, optimize, and on bad days recover, your data in a more incremental fashion than a single large table. It would also potentially allow you to distribute the data across multiple physical storage devices for improved speed - and while that may not be a concern up front, some day it likely will be if you intend to store things for long. You could even distribute data across multiple database servers or clusters if you structured it properly. You could also take advantage of MySQL's compressed table type for archival data, which would save disk space and potentially improve read speed if your data compresses well. Anyway, hope this helps. Let me know if I can answer any other questions about such a setup. Dan On 4/20/07, Michael Higgins <[EMAIL PROTECTED]> wrote:
Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue.... Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
