I would like to know what should be the best way to keep data on a server related to the following points:

  • Chat logs
  • Heavy text content
  • User references like amounts of ids (1,4,14,524,23220,...)

I'm using PHP and a mysql database but as obvious I know the topics above don't go well into mysql at large scale and maneuvering. So I would like to know how (no, i'm not asking for your work, just your 3-line orientation) exactly should I keep the data :)

Ok basically I keep my users in a sql table and another only for their friendships in which I have a field containing references related, allegedly, to chat logs. Now the thing is I don't actually understand whether I should have that content in a file, keep the file path in that sql field and then retrieve the file when needed, parse it and display it to the user or keep it in a database such as mongodb, raven, couch because I've never used no-sql database and wanted to know of experienced people on it. Same goes for the heavy text content and user references. For example, in my users table I have a field containing its friends in the following manner 1,4,5,6,14,51,... and since I've been told this is bad practice and certainly should be used whilst dealing with large amounts of data that would need to be changed constantly I came here in a hopeful act of guidance and enlightment.

  • 3
    What do you mean? mysql is perfectly capable of handling the data you describe. – Robert Harvey Jan 10 '14 at 22:19
  • @RobertHarvey Like a chat log certainly :) – user111671 Jan 10 '14 at 22:30
  • 3
    So what's the problem? – Robert Harvey Jan 10 '14 at 22:31
  • 1
    ...no. Nobody needs to give you a visual representation or anything. Voting to close, if you can come up with an answerable question other than "I wanna do X, do it for me" which isn't a question at all, I'll gladly vote to reopen. – Jimmy Hoffa Jan 10 '14 at 23:00
  • 1
    Who told you this site was for non-code matters? That's the opposite of what this site is for, we expect solvable problems here. Please point us to where on SO you were given that idea so we can notify whichever user referred you here for that to stop giving incorrect information about this site as they must not participate here. – Jimmy Hoffa Jan 10 '14 at 23:38

Having data in the structure "1,4,5,6,14,51,..." as one data value is a denormalized value.

Lets say you've got something that looks like:

Person2's friends are: 1, 4, 5, 7, 14, 51
Person3's friends are: 1, 5, 7, 15, 50

And so on. Now, lets ask the question "How many people consider person 5 to be their friend?" Well, in a denormalized structure, you're going to fetch every person's friends and then break it apart on the delimiter, and then see if 5 is in that list, increment the count and move on.

With a relational database, you've got a structure that looks like:

  +--------+     +----------+
  | user   |     | friends  |
  |--------|     |----------|
  | id (pk)|<-+--| from (fk)|
  | name   |  +--| to   (fk)|
  | ...    |     +----------+

And your query to answer this question is: select count(1) from friends where to = 5 And, well... you're done. You've looked over one small table that can be queried very quickly.

You've also got things if you want to cascade a delete to properly clean up the references in other tables (you've deleted user 5, make sure 5 is deleted from all the users). There's things like consistency, isolation and durability (part of ACID) that help ensure that your data maintains the proper structure.

NoSQL has its place. But its not a relational database and doesn't pretend to be. It also throws away the guarantees of ACID as a trade off for speed and ease of clustering (part of speed). There are times you don't care about ACID and instead want the API that is provided by a nosql database (i.e. you are creating an offline instance of an application and cache all web requests - your couchdb, being accessed as a web request means that the off line one doesn't need another database).

I would suggest reading the nosql tag on Martin Fowler's bliki (blog + wiki).

There are solutions where nosql fits quite well. LDAP is an ancient protocol that could be thought of as one of the earliest nosql databases that exists today. You don't access it via sql, but you do for storing data... hierarchal data. It really works well for such data, and very fast. Its got clustering and eventual consistency and all the things you think of when you hear about of nosql.

I wouldn't want to implement a chat system in ldap - its not the right structure. Trying to make relational databases do what ldap does is also not a fun process.

If you are thinking of doing this as a learning experience. Something to understand how nosql works, yea... go ahead. Try implementing a chat system in mongo or couch. Many people have. I wouldn't be surprised if SE's chat isn't backed by such a data store... though I'm not sure if its couch, or mongo... the domain of noSQL is quite large in that two nosql databases may share more in common with mySQL than with each other in design. Do chose a key-value database? or a column oriented one? or a documented oriented one? or a graph database? or... Wikipedia lists 10 different flavors of NoSQL with 5 different sub flavors of the key-value store.

I would suggest reading from SO SQL (MySQL) vs NoSQL (CouchDB) and chase the links and related links on that page.

If the data is relational... well, its likely a relational database that you are looking for (and you should make sure you learn about database normalization).

  • 1
    Now THIS was an answer. Just went over all the topics and discarded thoughtfully what I was thinking wrongly. Thank you very much, I'll assure you I'll get my database normalization to its max and take a good look at your advice! – user111671 Jan 11 '14 at 2:00

So here's an example. An item processing system at a bank will need to store the front and rear images of the check, but a single bank can process 6,000 items in a day (12,000) images. These images do not need to be retrieved very often, only in a research case.

From the storage perspective it makes more sense to store these on the file system rather than the disk because the data is not constantly changing, access can be slower and the database size does not grow by a few hundred gigabytes a day which affects time to restore backups.

The application needs separate logic to write and load images as opposed to metadata stored in the database and this is an additional cost to develop and a separate file system must be maintained.

Storing large blocks of text in the database is probably going to be cheaper to develop and manage than writing it out to a file system, but depending on the rate of change, retrieval needs, cost of storage vs. capital to purchase it you could make a case for storing it outside the database.

As a side note, storing lists in a single column of a database creates nightmares of programmers who have to parse them when they need a single value. Use the R in relational database management system to preserve sanity.

  • hello there, thank you for your answer, I've read it all and althought I'm not into financial application of software I do believe you when you refer to the price of high-scaled developed products and storage of designated files. I'll also try and make things more by the R, it's a shame actually to leave it like this :p thank you again! – user111671 Jan 11 '14 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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