0

I have pretty regular table users(id, login, password). And each user can upload pdf files (which must be less than 2 mb). That is reflected in table users_pdf:

id | pdf     | user_id
---------------------
1  | some_pdf|  1
2  | pdf2    |  1
3  | pdf3    |  3

Well, the question is: should I store filepaths to pdfs in pdf column, or real pdf files instead?

If I would store paths, that means dealing with file system folders, and it could be very painful sometimes (with backups, for example). If I would store files in the database itself, it will be slow, especially if the table will have a million entries or so, right?

So, what would you advise?

UPD. work with filesystem means folder per user, like

-users
|----user_id_1
   |--file.pdf
|----user_id_2
   |--file.pdf
   |--file.pdf
|----user_id_3
   |--file.pdf
   |--file.pdf

, so if i would have like a million users, that file structure will be death slow, right?

nukl
  • 10,073
  • 15
  • 42
  • 58
  • Similiar to: http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay – Shawn Mclean Feb 04 '11 at 04:50
  • File System for Files, Database for Data. Is there any confusion? – Nishant Feb 04 '11 at 04:53
  • 1
    The primary advantages of storing the file contents in the database is the guaranteed integrity and the fact that a single backup of the database covers all the files (and should be coherent - even if the DB is changing while the backup occurs; it that is a problem, get a better DBMS). – Jonathan Leffler Feb 04 '11 at 05:25
  • Lots of duplicates - e.g. see a very recent one: [Save files into a database](http://stackoverflow.com/questions/4888290/save-files-into-a-database/) – marc_s Feb 04 '11 at 06:28
  • we had a file system similar to that for court cases...we dumped it for a single folder since each file name is unique, why create a folder structure! We just store the file name in the database and use it to retrieve the document on the fly. – Leslie Feb 04 '11 at 15:33

2 Answers2

1

You said its less than 2mb, so storing files in table is an option. Although the best solution is to have a document management system like alfresco or even use an existing version management solution like svn. (rather than directly storing in the file system you need a Content Management System). You could just store the url of the document. This will help you in maintain, will avoid bloating of your database, will not affect your db performance and more importantly will simplify your programming, by leveraging existing solution ( did I say version management, in the of chance you might need it).

uncaught_exceptions
  • 21,712
  • 4
  • 41
  • 48
  • Also another important consideration. How many records do you foresee? Data bloating is a very real problem and you will have problems switching your solution once you decide to go with storing blobs. – uncaught_exceptions Feb 04 '11 at 04:51
1

Storing binary data is not necessarily slow. BLOBs and similar datatypes store a pointer to the data in the column, and the actual data somewhere else, so selects and updates are quite straightforward. I see no major disadvantages to storing the actual files in the database, as long as you do it correctly.