Going down the SQL road as a noob, what seemed like a walk in the park quickly turns into a multi headed dragon readying itself to breathe fire down your throat. Here’s your shield bitch.
Database vs Database Manager.
A database is a collection of data. Databases handle data in ways which allow systematic support for data manipulation and storage. Database managers handle databases. Within a project there are multiple considerations for managers: are multiple databases being accessed, does data representation need manipulation before add, what are the restriction levels of database access. Database managers are clutch.
Relational DBMS.
Lol. DBMS.
DBMS stands for Database Management System 🙃
Relational DBMSs define their structure as table relationships. An example: A box of tech shared within a family. The list of tech item data the family owns goes in its own table, the list of family member data go in another table. A relation can be formed between a family member and the tech they are currently in possession of. This structure makes adding a family member and adding tech a relatively easy addition. Insert a row of data, avoiding any database restructuring with previously existing data.
A note to keep in mind, Relational DBMSs work based off of schemas. The schema, predefines the table structure. When you want to add a new column to a table, a new schema must be defined and the preexisting data must be migrated to the new structure. For large databases with constantly changing structures, this can result in frequent downtime.
Relational databases work most effectively by knowing, in advanced, the structure of the table as well as the data points that will be used. Unstructured, unknown-in-advanced data struggles with this type of manager.
Relational Databases include SQLite, MySQL and PostgreSQL.
SQLite.
SQLite, is, light 😬.
As the name suggests, SQLite is a lightweight database for simple local data storage. Part of SQLite’s lightweight nature comes from its server less architecture. SQLite does not need to be installed before using. This zero configuration setup equals no configuration files.
SQLite is a self contained database with minimal support from external libraries or the operating system it’s running on. Embedded devices (phones, handhelds), internet of things, devices which often operate without human support, have environments which are suitable for SQLite.
MySQL.
MySQL was designed for speed and reliability, an ideal database for many use cases. MySQL’s speed and reliability comes at the cost of not being fully SQL compliant, depending on the use case there are some functional limitations. Many large applications such as Facebook have components powered by MySQL databases.
A few things to consider about MySQL, its license and propriety features, slowed development and SQL compliant and function limitation.
PostgreSQL.
PostgreSQL is “the most advanced open-source relational” DBMS, as per PostgreSQL 🙃. PostgreSQL is designed to be highly extensible and standard compliant.
PostgreSQL is relational, with a splash of object databases. Object DBMSs are structured to hold data as objects as opposed to loose pieces of language-independent data.
PostgreSQL is SQL compliant and has an effective way of handling concurrency (multi tasking) without read locks. A main fact to consider with PostgreSQL, memory performance, which quickly builds with each new connection. As read-heavy operations go, PostgreSQL’s structure is less performant than MySQL.
SQL. OR NAH? NoSQL.
If SQL is the fart from last month that permanently stained the couch, NoSQL is the morning fart that’s directly tied to last night’s bucket of sharp cheddar cheese which was washed down by a gallon of whole cow milk 🤮 But you appreciate the stench because of the added variety to the space.
NoSQL.
Not only SQL.
Relational DBMSs are effective when the structure is either known in advanced due to their predefined schemas. Need to make a structure change? Create a new schema and migrate old data to the new. Highly ineffective with large databases that have dynamic structures.
NoSQL on the scene: “I got the sauce”
NoSQL shines with agile. NoSQL databases have more options in handling complex data structures (document based, graph based, key-value pair, etc). NoSQL is an “unstructured” database. NoSQL databases are designed to handle unstructured data models in a scalable way.
SQL or Nah?
@thetealpickle on the internet. Namaste.
This article was brought to you by JESSICA JEAN JOSEPH © THETEALPICKLE
Top comments (5)
Adding a new column to an existing table doesn't force migrating the data. I don't know where you've got that information from but it's simply wrong.
sure.
However, changing schemas does, which was really the core take away 🙃
That depends on what you mean exactly by changing schema...
interesting,
I currently only know that any change to the schema => migration
how does the type of schema change influence migration? What are some of the different type of changes?
Actually, when working with Alter table (and not using the visual editors), I don't think there should be any data migration. In SQL Server, for instance, when you are changing an existing table using the visual editor built into SSMS, it might sometime migrate the data to a temporary location, drop the table, re-create it with the changes needed, and move the data into the newly formed copy of the table. However, when using the query editor to write Alter table statements, I'm not aware of such a process (that doesn't mean it doesn't happen - it could very well be that the query optimizer decides it's better to drop and recreate the table instead of simply altering it).