You're about to start a new database development project. What tools should you use?

When choosing a DBMS, choosing a system that will allow you to support multiple data types and applications while maintaining security and scalability is key. It's not just structured vs unstructured these days, it’s structured, unstuctured, support for vector search, spatial, graph, blockchain and more. Evaluating the application not just for its current state but also for growth can help choose a database that supports all of those data types. The best way to do so? By just adding a sql statement, and make sure it comes with tools that make it easy to use and even create the sql for you using AI. Oracle has a cloud database, the Autonomous Database that can help do this and it's free. It is also available on docker.

While focusing on the tools to build a database is required, one should also focus on how the data will be used by whom, and what tools are available to connect to the DB in the future. If we cannot use the DB for what it is intended ultimately, the added value is lost. So reporting, analytics and business requirements are critical in designing these systems.

It always depends on the project requirements. I always consider the budget, the architecture of the system as a whole and the skills of the teams involved. In the majority of the projects I worked the one that fit most of the projects was a relational/multi-model database system like Oracle (if the budget allowed) or MySQL (if budget was a constraint). In some minor and specific cases we used Cassandra and others Mongo. Sometimes there were specific databases required by the software vendor, so sometimes we had to choose SQL Server and PostgreSQL as well. It really depends on a majority of factors.

First of all, we need to know, how is the goals of the project? Because depeding on the answers, is recommended a specific DBMS. Nowadays many platform offers multiple support for the same database. But we have a classics. - SQL Server (Sql Server Management Studio) - MySql ( MySql Workbench) Also the multiple platforms, like DBeaver, Aqua Studio.

Choosing a database has many factors, some of which you may not have direct control as a mature company will have a strategic road map for its infrastructure this is important to prevent the widespread use of databases of which there are 100s of vendor/type combinations. We should also consider the current skill set of staff to support and develop, the initial and reoccurring support costs, the adoption of the database by others in your industry we want current/long term not niche. Ability to incorporate devops practices for database. Interoperability and being platform agnostic. lastly the 'self' management features and model desired IaaS vs PaaS vs SaaS.

A GUI tool for that certainly helps a lot since you can just click here and there without the need to know how to write the code to extract the same thing from a CLI. The data modeling tool isn’t everything you need, but I would definitely start with a tool that is easy to use with the tech that I’m using. That can be free or paid and I will pick the one will help me get the most out of my job. It’s worth spending time with trials to see which one fits best for you, if you are more experienced you probably already have a tool that is your favorite and sometimes it is just not worth spending the time to learn a new one. In my case those tools are Oracle SQL Dev Data Modeler, Erwin and MySQL Workbench.

Entity Relationship Diagram is the best way to get started with Database Design. Some tools available are 1. MS Visio 2. Lucid Chart 3. mySQL workbench 4. DbSchema

When initiating a new database development project, it's crucial to first thoroughly understand the business requirements. A key step in this process involves using a database design tool to meticulously outline each entity of the project and establish the relationships between tables. One effective tool for this purpose is JHipster. JHipster not only assists in database design but also integrates with other development tools, making it an ideal choice for creating a robust and well-structured database system.

When initiating a new database development project, it's crucial to first thoroughly understand the business requirements. A key step in this process involves using a database design tool to meticulously outline each entity of the project and establish the relationships between tables. One effective tool for this purpose is JHipster. JHipster not only assists in database design but also integrates with other development tools, making it an ideal choice for creating a robust and well-structured database system.

Graphical User Interface (GUI) These tools often provide a user-friendly GUI, making it easier for database designers and developers to visualize the components of their databases. This visual representation typically includes tables, columns, keys, relationships, and constraints, offering a clear and interactive way of managing complex structures. SQL Code Generation a significant feature of these tools is their ability to automatically generate the SQL code necessary to implement the database design. Error Prevention by providing a structured and intuitive interface for designing databases, these tools help in identifying and avoiding potential errors, inconsistencies, and redundancies in the database schema.

It always depends and there isn’t a single correct answer for that. Use the one you know and the one the works best with the database you are developing on. For Oracle my preferences are Oracle SQL Developer + sqlcl, Toad and PL/SQL Developer. It is best when combined with Liquibase and CI/CD. With Oracle you can also leverage Oracle’s Edition Based Redefinition for online code deployments, and this is a must for hot live systems, in my opinion and experience.

It is always recomended to use free tool by Oracle "SQL Developer", which helps devlopers to debug, execution, testing and write the big lines of code easily. However there are many other paid tools like Toad that makes developer works easier.

It is a good practice to use Database Development Environment, especially when testing scripts and debugging long scripts and Procedures.

For Oracle I leverage utPLSQL for unit testing. It’s free and developed by the Oracle community. Jacek and Samuel are/were behind it and before them it was Steve Feurstein. It’s the only one I used so far for unit tests. For load testing I used hammerDB and Swingbench for synthetic workloads and JMeter.

Considering Oracle, which is widely used for PL/SQL systems, these are some tools that can help with testing: Unit Testing: utPLSQL / ProTddOracle / DBFit Integration Testing: DBFit / SQL Developer Data Modeler Regression Testing: UTPLSQL / SQL Developer Performance/Load Testing: HammerDB / JMeter Security/Compliance Testing: Sonarqube

Again, ii depends on the support for the databases you’re using, the price of the tool and its capabilities. For Oracle I usually prefer using Oracle Enterprise Mannager, but have used Zabbix, Prometheus, SolarWinds, Datadog and custom/home made monitoring based on etcd and collectd too.

I would like to focus on the CI/CD approach. No matter which tool is in use, one choces either the desired state (e.g. MS DB Project, Red-Gate Flyway) or the prescriptive (Liquibase, EF migrations) approach. While the desired state is a useful tool for smaller projects, I found it really inconvenient in the long run. The biggest issue with the desired state is much more convoluted way of applying significant schema changes. Obviously, this is mostly a RDBMS concern, however, not everything can be efficiently done in a NoSQL DB. So, my CI/CD tool of choice is Liquibase.

Before choosing any tool first of all gather stakeholder requirement, questions like What is the business and what level of details do they need etc? After gathering user requirements then tools and systems are decided accordingly.