For us, we are thinking about the role of a DBA as someone who at least does the following:
- Manages the database implementation (which DB we use for what sets of data and their optimizations)
- Manages backups, protects queries, and handles server-side problems
- Investigates performance issues
What you're describing here is a pure DBA whose sole responsibility is the performance and reliability of the DB server; but no real stake in the applications that use the DB server.
While that is the textbook definition of a DBA (in a sufficiently large company), the rest of your question very quickly leads to requiring this person to also have a grasp on the BE side of things.
Providing input on how particular data should be structured in our db implementation (what columns, what kinds of types, and why - on as-is and to-be data)
The area you're pointing at is inbetween a DBA and a BE dev. The difference is the goal. Providing input on how particular data should be structured to maximize database performance is more on the DBA side of things. Providing input on how particular data should be structured to maximize BE performance (e.g. avoiding the need to launch contrived or multiple queries) is more on the BE dev side of things.
In smaller teams, the lines between roles are often muddied a bit as people usually don't have enough workload to stick purely to their own field, so in reality the distinction might not matter much. But if you're going to be looking for someone to work in the grey area, it's best to make sure they are (a) willing to and (b) able to.
Handling the restructuring of the data structure depending on system changes, application requirements, efficiency
This again lacks a distinction between actual goals and intentions. If it's a matter of optimizing a DB server, it's a DBA's job. However, if structural changes are made that change the data interface, the BE dev needs to obviously adjust the BE to accommodate those interface changes in their code.
If you want the task as you've described it to be handled by a single person, then you're again going to have to find a DBA willing to dive into the BE once in a while, or a BE dev with DBA experience.
My understanding is that the role of a software engineer, or at least a backend engineer, is simply to retrieve and process data, not necessarily to be responsible for deciding on its structure (on the database side, not application side). Is this a misunderstanding?
In my experience, yes this is a misunderstanding. It is the developers (or architects, who are a subset of developers) who decide on the data structure. The data structure is decided based on the business needs of the application.
A DBA of course has plenty of experience with relational databases and common issues, so they can weigh in. But even following your own description of a DBA's task, you don't mention business knowledge of the application anywhere.
Without business knowledge, a DBA cannot do anything but play an advisory part in a structure that was created by someone with business knowledge. They can only comment on a proposed database structure, they can't create it themselves.
its structure (on the database side, not application side)
Ideally, the two structures match. That's not always a given, but it's something a lot of dev teams strive for.
I don't want to break into a long debate on the pros and cons of things like Entity Framework vs Dapper vs stored procs, but it's clear that a significant part of the industry is moving towards an approach where the domain entities map to the database tables as best as they can, to avoid getting bogged down with the mapping of custom objects at every turn.
But you are right that sometimes, in complex situations, a dinstinction should be made for DB performance reasons. Highlighting that need is something you'd expect from a DBA, but it's important to remember that a DBA generally cannot singlehandedly tackle this problem without coordinating with a BE dev.
Or, alternatively, the DBA needs enough BE-experience to tackle the consequences of their DB changes themselves.
To my definition, it would mean that the chain of communication should work something like this on a small team:
- PM defines feature request
- internal discussions about what new data points need to be processed and how
- internal discussions about how to structure data
- data structure design finalized (what columns and why)
- DBA/SWE2 creates new data structure
- SWE1 implements new features processing newly exposed data structure
This is a waterfall approach. It's one way to do it, but most people nowadays prefer to go agile (or a milder variant thereof).
Especially in terms of performance, it's nigh impossible to nip every performance problem in the bud before it arises. Even if you design the perfect solution, the business requirements might change or the load on a particular feature of the application (and thus specific tables) might increase, which warrants you to reassess the solution you architected when you didn't know what the future would bring.
That being said, there are of course things you can prevent from the get go. As a basic example, you don't need to start off without any DB indexes and only add them if the application performance degrades. There are reasonable expectations that can lead you to automatically index certain columns as they are commonly used for lookup.
The main takeaway here is that while you can definitely prevent some problems, trying to prevent every problem is going to lead to overkill (and effectively will touch on YAGNI), on top of not being able to always foresee future changes in the business requirements.
In all cases, you should monitor the database server (= DBA) and monitor the changes to the BE and its business requirements (= BE dev) if you want to be able to respond to any performance degradation.
Does this mean that a DBA is required? Or just that SWE2 has to have particular domain knowledge regarding databases? Is there another term for this person and are they necessary? Should SWE1 simply learn the necessary steps of how data should appropriately be structured for efficient queries and scaling?
It seems to me like you need a big picture thinker, someone who can reasonably handle both the database and the impact on the BE. You're always going to have to be looking for a DBA with BE interests or vice versa as you're cherry-picking responsibilities from both roles.
It may also be a good idea to hire an architect (preferably with some DBA experience), as they will provide the big picture thinking that you're trying to implement in your proposed approach.
For reference, one of my coworkers is a DBA-turned-developer-turned-architect, and someone like him would be able to fulfill all the roles and responsibilities you've listed.