Some key concepts
OLTP
This stands for On Line Transaction Processing, it’s a very old term for an application that serves a lot of customers. A typical commerce or support app would be an OLTP app.
Relational database
The theorist Edgar F Codd created relational algebra to model how data can be stored once and retrieved efficiently all the way back in 1970. SQL is a partial implementation of his ideas and is what we’ve ended up with now. Read the Wikipedia article if you want to know more about it. If your database is modelled using Codd’s relational algebra it is a relational database. It’s made of relations between tables, that’s it.
RDBMS
This is software that supports the creation and management of relational databases, Relational Database Management System. So things like parsing SQL and turning it into result sets, plus the automation of updating and deleting data are done transparently without you having to do more than send it the SQL to do what you ask.
Transactions
Take an operation that updates information in a database. Taking the information and saving it, making sure it is saved consistently, is a transaction.
Before the web-based systems we use today using client/server software architectures meant the forms you wrote would keep a connection to the database open. Until the end user had hit a button that committed the all of the changes made they would be operating inside a transaction that could also be rolled back. This was one of the fundamental things about these systems that now seems completely alien. Making many changes across different tables that could then be undone if necessary was part of how the architecture worked. This was also true if you were running a command line SQL interpreter, you would have to explicitly commit depending on which RDBMS you were using.
The web is stateless in essence. This is why we need to have things like cookies so we can identify who is logged in and what they are allowed to do. Keeping a persistent connection is far too expensive and difficult at web scale. It also meant that whatever you sent using a form was sent in one transaction, if there is a requirement to save many pieces of data then you may find that you ask the user to use a wizard style interface that allows them to build up all of the data between steps and then finally save it all in one transaction at the end of the processing.
ACID Transactions
This is what transactions need to support to work correctly.
- Atomicity: Each transaction is treated as a single unit that either succeeds or fails.
- Consistency: The result of any series of transactions goes from one consistent state to another, with each following any validation rules.
- Isolation: Each transaction takes place in its own space and transactions do not interfere with each other. They will wait until the previous one is complete.
- Durability: Once data is committed it will remain committed, even in the event of system failure.
Some database systems have what’s called eventual consistency where there is a window in which the data could be inconsistent for operational reasons.
DDL vs DML
You rarely see these terms now, but the first stands for Data Description Language, which is the SQL for creating and changing how data is stored, as in the structure of tables and indexes, plus things you can’t undo like truncation. By definition you can’t roll this back once done. DDL was historically left up to the database vendors, this means the creation of databases and tables often has slightly different syntax between vendors. For example, Oracle has table spaces and Postgres does not.
Data Manipulation Language is the part where you create, update and destroy the data itself. The DML side of SQL is subject to an international standards body, and tends to be consistent across vendors, or at least it is if you stick to the standard.
Object-Relational Mapping
This is covered in detail in Fun with Object-Relational Mapping
Full table scan
This is simply having to read every row in a table in order to match some filter criteria. For small tables it may not matter, but larger tables that lack an index or partial index may well cause performance problems. This term is I think an Oracle one, when you look at explained plans in Postgres it’s called sequential scan. In essence it means looking at all the data in a table instead of going in through an index and cutting down the amount of information you need to process to find what you’re looking for.
Logical vs Physical models
Logical models show what the relationships would look like if we had an RDBMS that was sophisticated enough to model many to many relationships. Physical models show how the model is actually implemented. These days we tend to just do the physical model.