2

I'm developing a java web-app for the acquisition of contracts, and that have to receive some file from an external system. For each new contract i recieve some information about the new customer and a audio file. Now I could save the file on db(I'm using Oracle 11g) or save just the link on db and then maintain the file on a server.

In your opinion What is the better solution? What are prod and cons of the both the ways described above?

Improve this question
2
  • 1
    Does this contract change, gets amended or replaced? That's the only case I'd store externals in the database - (not so) cheap versioning included. Otherwise you'd be wasting space & performance by lugging this around in oracle.
    – mabi
    Commented Sep 29, 2014 at 7:54
  • Don't forget backup and recovery complexity. Stored in the DB, you get that "free". Stored outside, you need to make sure the files are backed up -- and that in a way that is consistent with the metadata stored in the DB.
    – Colin 't Hart
    Commented Sep 29, 2014 at 7:58

3 Answers 3

Reset to default
5
+25

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies).

This means however, you need a way to serve the files to your clients, which requires extra work (but also extra opportunities, as you are not bound to the DB communication protocol).

A pro for storing in the db is obviously the simple way to return the data to the client.

Improve this answer
1
  • 2
    Should you choose to do this be careful with your backup and restore strategy. The onus for ensuring everything in the file system is mentioned in the DB and vice versa now rests with you alone. Similarly there is no transaction which spans DB and file system as you add or remove file and meta data. You will have to write reconciliation and tidy-up code yourself.
    – Michael Green
    Commented Oct 6, 2014 at 3:59
2

Have you considered Oracle's BFILE ? Sounds like it's a perfect fit for you;

http://www.orafaq.com/wiki/BFILE

Here's some further reading material on the subject ; http://docs.oracle.com/cd/B10501_01/appdev.920/a96591/adl12bfl.htm

Improve this answer
1
0

As always, It will depend on how much money are you able to spend. (may I say "invest"?)

Although the feature of storing binary files into Oracle is included in all Database editions, storing binary files into the database will increase hardware and human resources costs since your db will become bigger and bigger, and it's very common in these cases that you'll be facing a VLDB in OLTP environment in a few months. These kind of databases tend to require high performance servers, big and expandible data storage subsystems, and people skilled enough to handle and prevent problems and tuning performance issues.

The benefits are obvious. Your binary data will be managed by a powerful, secure and flexible RDBMS such Oracle. Not to mention that your data will be high consistent with the metadata, widely reducing minor or big human errors, and maintenance will be simplified.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.