Why Does Everyone Still Focus on Relational Databases?
-
@travisdh1 said in Why Does Everyone Still Focus on Relational Databases?:
@dafyre said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller said in Why Does Everyone Still Focus on Relational Databases?:
@momurda said in Why Does Everyone Still Focus on Relational Databases?:
How are data in NoSQL db stored, referenced, retrieved?
In helpdesk relational db, i have tickets, tickets are created by users, users have relationship with other tables by keys. These relationships allow me to get meaningful data if it want by using joins and queries.
How would all this be stored in NoSQL? Does each ticket created become its own 'document' or entity blob, with the metadata such as user, assigned tech, date, etc available for query without having to use a join?Yes, that is generally how it works. Some things, like the username, might be an ID that is then retrieved from somewhere else, maybe not even the same database.
And this is where I get lost on the usefulness of the NoSQL Concept in general... How do I join up that data if I switch from using the user's name, to the User's ID?
One MySQL Query That may take three seconds becomes three MongoDB queries that the application then has to process, which could take 2 seconds each.
Do yourself a favor, and forget about joins. They're a coding and performance nightmare waiting to happen, from the little I've seen. You can do the same things with simpler logic, faster, even within the database engines often times.
The data still has to be combined somewhere... Whether that be in a join, or in the application.... There's still effort that has to be put in to ensure you're pulling the correct data.
Doing the Joins pushes that out to the RDMS server... Doing it manually from MongoDB would push that back into the application code.
-
@dafyre said in Why Does Everyone Still Focus on Relational Databases?:
@travisdh1 said in Why Does Everyone Still Focus on Relational Databases?:
@dafyre said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller said in Why Does Everyone Still Focus on Relational Databases?:
@momurda said in Why Does Everyone Still Focus on Relational Databases?:
How are data in NoSQL db stored, referenced, retrieved?
In helpdesk relational db, i have tickets, tickets are created by users, users have relationship with other tables by keys. These relationships allow me to get meaningful data if it want by using joins and queries.
How would all this be stored in NoSQL? Does each ticket created become its own 'document' or entity blob, with the metadata such as user, assigned tech, date, etc available for query without having to use a join?Yes, that is generally how it works. Some things, like the username, might be an ID that is then retrieved from somewhere else, maybe not even the same database.
And this is where I get lost on the usefulness of the NoSQL Concept in general... How do I join up that data if I switch from using the user's name, to the User's ID?
One MySQL Query That may take three seconds becomes three MongoDB queries that the application then has to process, which could take 2 seconds each.
Do yourself a favor, and forget about joins. They're a coding and performance nightmare waiting to happen, from the little I've seen. You can do the same things with simpler logic, faster, even within the database engines often times.
The data still has to be combined somewhere... Whether that be in a join, or in the application.... There's still effort that has to be put in to ensure you're pulling the correct data.
Doing the Joins pushes that out to the RDMS server... Doing it manually from MongoDB would push that back into the application code.
Doing it in the application provides a lot of power, and a lot less locking.
-
@scottalanmiller said in Why Does Everyone Still Focus on Relational Databases?:
@dafyre said in Why Does Everyone Still Focus on Relational Databases?:
@travisdh1 said in Why Does Everyone Still Focus on Relational Databases?:
@dafyre said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller said in Why Does Everyone Still Focus on Relational Databases?:
@momurda said in Why Does Everyone Still Focus on Relational Databases?:
How are data in NoSQL db stored, referenced, retrieved?
In helpdesk relational db, i have tickets, tickets are created by users, users have relationship with other tables by keys. These relationships allow me to get meaningful data if it want by using joins and queries.
How would all this be stored in NoSQL? Does each ticket created become its own 'document' or entity blob, with the metadata such as user, assigned tech, date, etc available for query without having to use a join?Yes, that is generally how it works. Some things, like the username, might be an ID that is then retrieved from somewhere else, maybe not even the same database.
And this is where I get lost on the usefulness of the NoSQL Concept in general... How do I join up that data if I switch from using the user's name, to the User's ID?
One MySQL Query That may take three seconds becomes three MongoDB queries that the application then has to process, which could take 2 seconds each.
Do yourself a favor, and forget about joins. They're a coding and performance nightmare waiting to happen, from the little I've seen. You can do the same things with simpler logic, faster, even within the database engines often times.
The data still has to be combined somewhere... Whether that be in a join, or in the application.... There's still effort that has to be put in to ensure you're pulling the correct data.
Doing the Joins pushes that out to the RDMS server... Doing it manually from MongoDB would push that back into the application code.
Doing it in the application provides a lot of power, and a lot less locking.
Moar flexibility too. I'm building (okay, tinkering with at this stage) a helpdesk in Meteor, just to see how it works in practice. In theory, it sounds good... We'll see how it works out in practice, lol.
-
It's my understanding that the main difference between relational and nosql is that relational is rigid and nosql is not.
If you design a relational DB, it's made up of tables, with certain data types, in a 2D structure of columns and rows, columns that are predefined for the table.
If you have a table with 50 columns, one row might have data in 25 columns and another row has data in 35 columns, but both rows have all 50 columns of data (even if blank, empty, null, etc)
In a nosql or "graph" type dataset, you don't necessarily worry about columns at all. One page might need 25 pieces of data and another page needs 35 pieces of data, no more, no less. More can be added later, or removed later. Not only that but each record may have completely different data.
I also think of nosql as somewhat 3D. You forgo the 2D relational style of rows/columns where multiple tables are linked by column IDs. Instead, ALL the data is stored in a 3D way in a single document, or graph. This is really the difference between something like CSV versus XML. Because XML can have sub-data and sub-sub-data all the way down.
In a relational database, if you have a "customer" table and want to allow that customer to have as many addresses assigned to them as they like, they you would be required to build a separate "addresses" table, then link records by user ID. If you wanted to delete addresses when a user is deleted, you'd need app logic or a foreign key relationship. But if you didn't delete the related data, you could have orphaned rows of data, lose those relationships, and the data starts to get ugly over time with bugs sneaking in.
In nosql, you just pull up the customer data and all their addresses are available in the record. They can have no addresses, or 30, doesn't matter.
If you have a ticketing system in nosql, then all data just gets stored in 3D like JSON or XML structures.
So the big question, what if you want to find all tickets by a certain user? Well that's what the nosql engine handles, you just search for the user and it finds all the records.
Should records be stored by ticket or by user? I would think it's by user, actually, and all the tickets and all the sub-information about those tickets get stored all the way down the line.
MongDB is essentially a JSON storage engine, that's exactly the type of data you store. It also means it's limited. If you have multiple nested levels, perhaps hundreds of bits of data, you can't just replace one small bit of data that changes, you have to write the entire document again.
MongoDB is perhaps the best choice for people who typically use relational but finding predefined columns a bit limiting.Redis excels for rapidly changing data but limited DB sizes. It works best when the entire DB can fit in available memory space. The data structure is more aligned with key:value type data.
Cassandra for when you just need to store a bazillion records, like analytics, hit counts and logging. Data can have its own expiration date set.
ElasticSearch is also JSON but can save parent and child documents and has more advanced searching when relating records together.
CouchDB is good for accumulated data that doesn't change much and you want to run the same queries all the time. Master-master replication is possible for multi-site deployments.
And yes I got all this info from a nifty chart here which is pretty handy: https://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis?
Personally I haven't found a project that doesn't very easily fit in the relational DB mold. Most data, I think, is still relational, where some list of stuff belongs to or relates to some other list of stuff. As long as the idea of predefined data (columns) applies to the "list of stuff", relational is still good!
However, if the "list of stuff" is terribly random, such as "list of stuff in a person's closet" versus "list of stuff intalled in a PC" then it makes more sense to go nosql.
List of stuff in PC -> defined columns ["cpu", "RAM", "HDD", "Video Card"......]
List of stuff in a closet -> undefined columns [ {name: "shirt", color: "black"}, {name: "shoebox", contents: "pictures"}, {name: "vacuum"}, ........]It's all so magical!
-
Since I'm guessing what spurred this train of thought was my topic, I'll add in my reason for wanting a relational database. It is, as others have stated, because it is what I know. I know how to design, pseudo code, build, and normalize a relational database. I don't even know where to start converting it into a NoSQL, JSON system. It sounds great, but the curve is daunting for a side project to improve my efficiency.
-
@Kelly said in Why Does Everyone Still Focus on Relational Databases?:
Since I'm guessing what spurred this train of thought was my topic,
In all honestly, I have no idea what prompted this.
-
I think that it was a conversation about SQL Server on SW with PHP.
-
@scottalanmiller Alrighty. I figured this topic had something to do with the question: https://mangolassi.it/topic/10345/software-package-or-platform-suggestions since I was insisting on a relational DB.
-
@Kelly said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller Alrighty. I figured this topic had something to do with the question: https://mangolassi.it/topic/10345/software-package-or-platform-suggestions since I was insisting on a relational DB.
I thought that I wrote this first. Maybe not. But I doubt that that was the impetus.
-
@Kelly said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller Alrighty. I figured this topic had something to do with the question: https://mangolassi.it/topic/10345/software-package-or-platform-suggestions since I was insisting on a relational DB.
Wait, this thread is 40 threads before that one, lol. How could it have been based on that?
-
@scottalanmiller said in Why Does Everyone Still Focus on Relational Databases?:
@Kelly said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller Alrighty. I figured this topic had something to do with the question: https://mangolassi.it/topic/10345/software-package-or-platform-suggestions since I was insisting on a relational DB.
Wait, this thread is 40 threads before that one, lol. How could it have been based on that?
It just appeared at the top of my unreads and I didn't bother checking the posted data, so I made an assumption
-
@Kelly said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller said in Why Does Everyone Still Focus on Relational Databases?:
@Kelly said in Why Does Everyone Still Focus on Relational Databases?:
@scottalanmiller Alrighty. I figured this topic had something to do with the question: https://mangolassi.it/topic/10345/software-package-or-platform-suggestions since I was insisting on a relational DB.
Wait, this thread is 40 threads before that one, lol. How could it have been based on that?
It just appeared at the top of my unreads and I didn't bother checking the posted data, so I made an assumption
Maybe me talking about databases prompted YOU to think about doing your project!
-
@guyinpv said in Why Does Everyone Still Focus on Relational Databases?:
It's my understanding that the main difference between relational and nosql is that relational is rigid and nosql is not.
If you design a relational DB, it's made up of tables, with certain data types, in a 2D structure of columns and rows, columns that are predefined for the table.
If you have a table with 50 columns, one row might have data in 25 columns and another row has data in 35 columns, but both rows have all 50 columns of data (even if blank, empty, null, etc)
In a nosql or "graph" type dataset, you don't necessarily worry about columns at all. One page might need 25 pieces of data and another page needs 35 pieces of data, no more, no less. More can be added later, or removed later. Not only that but each record may have completely different data.
I also think of nosql as somewhat 3D. You forgo the 2D relational style of rows/columns where multiple tables are linked by column IDs. Instead, ALL the data is stored in a 3D way in a single document, or graph. This is really the difference between something like CSV versus XML. Because XML can have sub-data and sub-sub-data all the way down.
In a relational database, if you have a "customer" table and want to allow that customer to have as many addresses assigned to them as they like, they you would be required to build a separate "addresses" table, then link records by user ID. If you wanted to delete addresses when a user is deleted, you'd need app logic or a foreign key relationship. But if you didn't delete the related data, you could have orphaned rows of data, lose those relationships, and the data starts to get ugly over time with bugs sneaking in.
In nosql, you just pull up the customer data and all their addresses are available in the record. They can have no addresses, or 30, doesn't matter.
If you have a ticketing system in nosql, then all data just gets stored in 3D like JSON or XML structures.
So the big question, what if you want to find all tickets by a certain user? Well that's what the nosql engine handles, you just search for the user and it finds all the records.
Should records be stored by ticket or by user? I would think it's by user, actually, and all the tickets and all the sub-information about those tickets get stored all the way down the line.
MongDB is essentially a JSON storage engine, that's exactly the type of data you store. It also means it's limited. If you have multiple nested levels, perhaps hundreds of bits of data, you can't just replace one small bit of data that changes, you have to write the entire document again.
MongoDB is perhaps the best choice for people who typically use relational but finding predefined columns a bit limiting.Redis excels for rapidly changing data but limited DB sizes. It works best when the entire DB can fit in available memory space. The data structure is more aligned with key:value type data.
Cassandra for when you just need to store a bazillion records, like analytics, hit counts and logging. Data can have its own expiration date set.
ElasticSearch is also JSON but can save parent and child documents and has more advanced searching when relating records together.
CouchDB is good for accumulated data that doesn't change much and you want to run the same queries all the time. Master-master replication is possible for multi-site deployments.
And yes I got all this info from a nifty chart here which is pretty handy: https://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis?
Personally I haven't found a project that doesn't very easily fit in the relational DB mold. Most data, I think, is still relational, where some list of stuff belongs to or relates to some other list of stuff. As long as the idea of predefined data (columns) applies to the "list of stuff", relational is still good!
However, if the "list of stuff" is terribly random, such as "list of stuff in a person's closet" versus "list of stuff intalled in a PC" then it makes more sense to go nosql.
List of stuff in PC -> defined columns ["cpu", "RAM", "HDD", "Video Card"......]
List of stuff in a closet -> undefined columns [ {name: "shirt", color: "black"}, {name: "shoebox", contents: "pictures"}, {name: "vacuum"}, ........]It's all so magical!
I like magic! 8-)
-
I just built out a NoSQL ElasticSearch three node cluster in the lab. Whoo!
-
I've done direct management of big MongoDB, Redis, Cassandra and ElasticSearch systems in the past. I've touched others, but those are the big ones.
-
@scottalanmiller said in Why Does Everyone Still Focus on Relational Databases?:
I just built out a NoSQL ElasticSearch three node cluster in the lab. Whoo!
The perfect DB setup for storing a hit counter for the number of times I've watched Tombstone!
