0

Which is the better way to store user profile data in a MySQL database or in folders and why. I am making an advertisement website which has user ads how to store that ads.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
user3610919
  • 33
  • 3
  • 7
  • What is the problem of using a database? – Ed Heal Jun 14 '14 at 07:01
  • there is no problem in using database but all the big website uses folders to store user post,photos etc – user3610919 Jun 14 '14 at 07:14
  • @EdHeal Calling you out here similar to the way I called you out on my answer in the comments. If you feel you have a real answer that can explain the benefits of your method, please share them in a real answer. But this comment trolling will not win you respect or aid the original poster in dealing with this issue otherwise. – Giacomo1968 Jun 14 '14 at 07:34
  • @JakeGould - Not trolling but highlight an inaccuracy. – Ed Heal Jun 14 '14 at 07:40
  • @EdHeal “Not trolling but highlight an inaccuracy.” Step back & look at what you are doing. You are trolling. On this site if you believe something is inaccurate you can comment as well as down vote or even—I know this will shock you—but post your own answer. Your comments add up to trolling. If you gave a valid perspective that would counter mine & aid the original poster, post it as an answer. Simple as that. – Giacomo1968 Jun 14 '14 at 07:42
  • 1
    @JakeGould - The other answers/comments everything moreorless than what i would write – Ed Heal Jun 14 '14 at 08:10

3 Answers3

2

Which is the better way to store user profile data in a MySQL database or in folders and why. I am making an advertisement website which has user ads how to store that ads.

Both. You should use MySQL for database storage of URLs, ad ids, customer info and sundry info like that. But the actual ads themselves—which I assume will be images—should be stored on the file system in folders/directories. You should never store images in a database as BLOBs (binary large objects). Always store and serve the image content via the file system.

To explain why you should not ever store your images in a database, a web server reading an image file from a file system will be faster and less error prone than a system where a database is storing images that are then fetched by a script and then served by the server. You get the additional overload of not only MySQL having to serve the raw image but the help scripts having to be the middleman. A URL connected to a file system resource can be served much quicker off of a web server.

Additionally, database size is under control without images stored as BLOBs in there. Which makes backups and recovery much easier to handle.

This can seem to be a slight religious debate—as evidenced in this question and answer thread here—but in general:

  • Images are files and should be stored on a file system where a web server can pick them up easily.
  • Data should be stored in a database where that content can be fetched & manipulated easily.

But that said, why reinvent the wheel? You should look into great open source systems like Revive Adserver (formerly OpenX Source).

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
  • 1
    _You should never store images in a database as BLOBs (binary large objects)._ Could you please substantiate that? – VH-NZZ Jun 14 '14 at 07:07
  • @okiharaherbst A web server reading an image file from a file system will be faster & less error prone than a system where a database is storing images that are then fetched by a script & then served by the server. You get the additional overload of not only MySQL having to serve the raw image but the help scripts having to be the middleman. A URL connected to a file system resource can be served much quicker off of a web server. – Giacomo1968 Jun 14 '14 at 07:09
  • More info & discussion in this thread. http://stackoverflow.com/a/1010660/117259 – Giacomo1968 Jun 14 '14 at 07:12
  • 1
    There is no evidence that this is the case that both slower and also error prone. So the database size *May* be under control but what about the file space. Additionally just using a database you can ensure that deleting an advert all the relevant bits are also deleted (images) thus controlling the overall data size requirements – Ed Heal Jun 14 '14 at 07:14
  • @EdHeal See my last—and final—say on the subject. This can be a slight religious debate, but I stand by my real world experience as someone who has deployed dozens of systems including many ad servers. Database storage of images for high performance needs like ad servers is just never advisable. I have always had less issues with data in a database, and files on a file system than otherwise. – Giacomo1968 Jun 14 '14 at 07:16
  • I know the other thread and thanks for editing your post. However, I vehemently reject claims that the overhead, if any, is not worth the candle, esp. if you're thinking in terms of RDBMs built-in capabilities likes replication, sharding, data integrity (see @EdHeal's comment above) and many others. In the end you're really only streaming data in an HTTP response so where the data comes from is of little importance. I'd much rather have a cluster of RDBMs cleverly replicated to back a web app than static content servers delivering from the file system. But others prefer it hard the way. – VH-NZZ Jun 14 '14 at 07:22
  • @okiharaherbst “But others prefer it hard the way.” Sounds fantastic! – Giacomo1968 Jun 14 '14 at 07:23
  • @JakeGould - Data integrity is more complex using both. Also able to deploy load balancing and database replication. – Ed Heal Jun 14 '14 at 07:24
  • @okiharaherbst And to be specific you say, “I know the other thread…” so what this is what I said, a personal preference. If you know that thread, then why jump on someone else? Are you simply stalking? So the reality is you’re just trolling me on my opinion. So do as you wish! I provided an answer. If yo disagree with it, I openly welcome you to down vote it. Or perhaps post your own answer, but the approach & tone of you and Ed Heal here is pitch perfect pedantry. Do you want to argue with me? Or provide the original poster an answer? Or is there something else? Stuff to think about. Cheers! – Giacomo1968 Jun 14 '14 at 07:26
  • 1
    I mean no offense, but really, most data models are relational by nature (sorry to those NoSQL fans, that's just been mathematically proven since the 70s). If you have a way to ensure data integrity between your database and your filesystem in a way that is equally as straightforward as using foreign key constraints and transactions, then I'm listening. Of course, we're talking about replicated systems here to have load balancing. – VH-NZZ Jun 14 '14 at 07:28
  • @okiharaherbst “If you have a way to ensure data integrity between your database and your filesystem in a way that is equally as straightforward as using foreign key constraints and transactions, then I'm listening.” You seem to miss the point: Your tone & attitude is simply trolling. Your theorehtical concepts fly in the face of practical realities I have met. I did not post this answer to deal with childish pedantry, so like I said please feel free to down vote my answer. Or post your own! Have a wonderful weekend! – Giacomo1968 Jun 14 '14 at 07:31
  • 1
    @JakeGould Sorry that you took that way, that's not the way I meant it. However, anyone using words like _Always_ or _Never_ should be ready for some serious testing. That's just the nature of the thing. I will not vote down your answer because the I find its message useful (= you're not saying anything blatantly wrong) but I just happen to disagree with your advice _to never store images in a database_. This not only sounds wrong but it's also just one of those one size fits all paradigms that don't pair well with programming, as you should know. – VH-NZZ Jun 14 '14 at 07:34
2

Databases are made to:

  1. Reduce overhead for ideas like the folders concept you're describing
  2. Make the data incredibly fast to search (I think MySQL uses B-Trees to index it's data).
  3. Relate data to other data, and a whole host of other things.

You should definitely use a database, and MySQL is a great place to start. It's widely supported and is a good base database to know. You should refer to documentation on how to use MySQL. If you need a better backend system I also recommend Django.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
robert
  • 819
  • 1
  • 10
  • 24
0

You choose the solution which best fits the problem. Assuming these adverts are in video format (?) the most obvious solution too me would be to store the videos on a server in folders, then map the absolute file path of each advert to the associated user id and the user profile data which would be stored in the database.

Traditionally, the point of a relational database is to reduce data redundancy and increase data integrity, which are benefits you wouldn't get using a flat file approach

ConMan
  • 1,642
  • 1
  • 14
  • 22