10

In the context of web development, who designs databases? Despite a whole host of information associating back-end web dev with server-side processing, data modeling, and the like, the database design aspect of the equation seems to be mysteriously absent.

I am not talking about who sets up the physical database, I'm referring to who designs the logical model of the database, conducts user-story interviews to get information on what fields are needed, what those field specifications are, and etc...

I've realized that the (PROPER) design of a database is no small task (I'm reading this 672 pager) and could easily be an entire profession. However, searching up and down the internet has led to surprisingly little results for who is expected to handle this task in the context of web dev.

closed as primarily opinion-based by gnat, Robbie Dee, TZHX, Thomas Owens Aug 31 '16 at 14:56

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    It's so very dependent on the organization and which departments and structure it uses - in my experience it's usually back-end developers/solution architects and if there's a database department, in connection with DBAs or specific database developers. – Allan S. Hansen Aug 31 '16 at 6:36
  • what those field specifications are, if your project have a spec, you should be able to identify your model in. – Walfrat Aug 31 '16 at 6:36
  • 1
    I would say, these days, whatever framework you're using dictates your database design. – Pieter B Aug 31 '16 at 7:32
16

Your question is not only pertinent to webapps, but to all kinds of apps that use a database backend.

In my experience

  • The design of the database is the combined work of developers and DBAs.
  • Developers make rough, or often very good, database designs. It all depends on the experience of the programmers.
  • Often developers create tables in their development database and a conceptual model is reverse-engineered from it.
  • Often conceptual ER diagrams are discussed with other team members, often clients. In this stage obvious conceptual errors are detected and hopefully figured out and fixed.
  • The DBA's work is to review such a design, and fine-tune it to detect violations of normal forms.
  • Also the DBA enforces naming conventions of tables and columns
  • Also the DBA envisions possible performance bottlenecks and tries to understand how the data will be queried in order to create appropriate indexes later.
  • The revisions/checking/fixing cycle between developer/app designer and DBAs goes through several iterations until the model is mature enough to generate a physical model.
  • Usually a database design tool is used in all but very small databases to help this process.

Bottom-line:

  • Developers know the business and problem domain better than DBAs, so they very much do most of the initial design and, depending on the experience of the developer such a design can be very close to the final design.
  • The DBA's role is mostly to ensure NF, naming conventions, performance considerations, to correct obvious errors and finally to generate a physical model, then a database specific script, and to run these to create the database.
  • I'd say it's 80% developer and requirement analysis work and 20% DBA work.

UPDATE:

There are 3 kinds of DBAs:

  • development DBAs that know data-modeling, are SQL experts and can write stored procedures, they usually are former developers;
  • production DBAs that specialize in installing, performance tunning, backups and restoring, etc.,
  • and jack-of-all-trades DBAs that have worked doing all those things and as such can do most (they are very few).

Most DBAs are production DBAs, which is good beause they are the guys who recover lost databases from failed disk arrays in the wee hours of the night. But they don't participate actively in the design process.

  • 2
    This is considering you have a DBA that can do that, where i am and i was before, there was none to help for that. – Walfrat Aug 31 '16 at 6:35
  • 2
    @Walfrat You are right. There are 3 kind of DBAs: development DBAs that now data-modeling, are SQL experts and can write stored procedures; production DBAs that specialize in installing, performance tunning, backups and restoring, etc. and jack-of-all-trades DBAs that has worked doing all those things and an such can do most. Sadly, most DBAs are only production DBAs. – Tulains Córdova Aug 31 '16 at 6:40
  • 9
    You forgot the "we-can't-afford-a-DBA" where the shop is so small that some developer maintains the database on the side. That's very common too. – Sebastian Redl Aug 31 '16 at 7:57
  • 1
    What "NF" stands for? – BЈовић Aug 31 '16 at 11:04
  • 1
    Normal Form, see also wikipedia. – Friek Aug 31 '16 at 11:18
4

It depends what is the database used for.

In many applications (web apps or not), the database is intimately tied to that application because it serves as the persistent store for it. Then the database is conceptually part of the application so is designed together (and you presume that no other program would significantly access or update that database). BTW, persistence could be achieved by other means than a database, e.g. plain textual files, binary files (notably indexed files à la GDBM), git (or other VCS) repositories, directories or file trees, raw disk partitions, dedicated hardware (e.g. flash), remote file systems, checkpointing techniques. For databases designed for and with one application, you should care about common retrieval & update patterns and design the database schema (and indexing!) with them in mind.

In some situations the database is by itself a major and independent asset, and is designed a priori to be used by several different applications (and even future ones). Then it should be designed independently (and much more carefully).

In particular some web apps are just web interfaces to existing databases.

In many cases (think of some wiki as an example), the data is more important and more valuable than the application(s) using it. You may care about how to make it future proof and be able to evolve it easily (e.g. by using or defining textual & versatile -preferably standardized and documented- formats to backup and restore it).

I've realized that the (PROPER) design of a database is no small task...

Read also about NoSQL, document-oriented databases, key-value databases, knowledge management, knowledge representation and reasoning, ontologies, expert systems, business rules approach, ERP, CMS. Perhaps consider using REDIS, MongoDB, etc..

  • Not sure. I didn't do it. Thanks for those last few links! – the_endian Aug 31 '16 at 8:07
  • IMHO, the links in the first paragraphs are worth reading too. – Basile Starynkevitch Aug 31 '16 at 8:15
  • Yeah, I'm not sure why but it seems this stuff isn't "covered" nearly as much in developer courses as it could be. To the point where I'm sure there are some serious database hackjobs going on out there in web dev land. They teach you how to use the DBMS, but not as much about the stuff we talked about here. – the_endian Aug 31 '16 at 8:18
  • @TeeSee I can assure you, it's not just web dev land where there's serious hack jobs going on with databases. But yes, there's a lot of it goes on, generally for business reasons, like keeping initial costs down (forgetting about maintenance and technical debt) – gabe3886 Aug 31 '16 at 11:24

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