Tag: Sql
Why Your Company Needs a Database Modeling Tool
Why do companies use data modeling tools? The short answer: To save costs, gain efficiency and stay competitive in their market. In this article, you’ll learn about all the benefits your company can reap by investing in a leading database modeling tool.
Good database modelers need only one tool: their knowledge and ability to conceive data models that solve real-world problems. Eventually, they need to translate their designs into artefacts that help communicate ideas and concepts.
What Is a DDL Script? How Do You Use It?
What is a DDL script and why is it so crucial for systems that use relational databases?
DDL scripts are widely used in creating, documenting, and migrating systems that use relational databases. They can be designed for simple tasks, such as migrating a database from one server to another, or for more complex tasks.
DDL scripts are read sequentially and are written in SQL with commands specific for defining data structures (CREATE, ALTER, and DROP).
How to Store Login Data in a Database
Figuring out authentication is part of a secure data storage strategy. Find out how to store auth data safely in your database.
Almost every application requires user authentication; that is why authentication data storage is a common feature of database and application design. Special attention is needed to ensure data is kept secure and to avoid breaches that can compromise sensitive information.
Do I Need Authentication or Authorization? Although both words are frequently used in a similar way, they do not mean the same thing.
Sharing ER Diagrams With Your Team in Vertabelo
Data modeling is a collaborative effort, active or otherwise. Effective ER diagram collaboration requires sharing the progress with team members and others who may not directly contribute to the model, such as program managers, product teams, and business analysts.
You need a data modeling tool with hassle-free sharing and collaboration capabilities. Sharing ER diagrams and sharing database models need to be a key feature of any data modeling tool.
How to Create a SQL Script in Vertabelo
Data modeling is usually seen as a visual activity, but many data modelers prefer writing a SQL script to generate the data model. Catering to different teams and different ways of collaboration between teams, Vertabelo offers you three different options to create your data models. You can create a physical data model, a logical data model, or a SQL script. Creating a physical data model or a logical data model consists of a GUI-based approach.
Using Vertabelo’s SQL Script Editor
Vertabelo’s new SQL Script tool lets you write and save SQL scripts and run them against a connected database.
Creating an SQL Script Start by clicking on the Create Document button. It’s the first one on the main toolbar.
The New Document window appears. Now select SQL script and click on the Create button.
The New SQL script window will be displayed.
Name your script using the Name field and click the CREATE button.
Hire or Get Hired: A Data Model for the Recruitment Process
No matter which side of the equation you’re on, sometimes it’s tough to find a qualified person for a specific job. In this post, we look at a data model to help recruiters and HR departments stay organized during the hiring process.
Most of us have been involved in the hiring process – most often as the job applicant. However, we can also find ourselves involved on the hiring side, maybe by testing the applicant’s technical knowledge.
A Database Model for a Renting Service
Renting goods and services is very popular today. Services like Airbnb rubbed the renting lamp and let the genie out, especially for travelers. They’ve opened up new horizons, and in the future we can expect that all kinds of rental services will become even more common. In this article, we’ll describe a database model that could be used to run an application for renting apartments, rooms, and anything else you can think of.
A Data Model for a Medical Appointment Booking App
Booking a doctor’s appointment using an online app is an innovation that simplifies the entire process. Let’s dive into the data model behind an appointment booking app.
Why use an app? It makes it easier for people to find the doctors of their choice, letting them see the doctor’s professional records and patient reviews. When someone finds a doctor they like, they can book an appointment with them without leaving the app.
An Event Management Data Model
Organizing an event is a lot of work! In this article, we examine the data model behind an event organization app.
If you’ve ever tried to organize an event for more than ten people (and don’t count parties or business meetings here) you know how complicated event management can be! Have we invited everyone? Have they confirmed if they are coming? Is the venue booked and prepared? Who will host the event?
A Real Estate Agency Data Model
Other than location, what’s it take to run a successful real estate business? We examine a data model to help real estate agencies stay organized.
Buying, selling, and renting apartments or houses is really big business today. Most people are happy to pay a fee and let a professional real estate agency do the work for them. On the other hand, the company could act in its own behalf, buying properties to resell or rent.
A Data Model to Keep Track of Your Most Precious Possession
Being healthy and fit is a lifestyle, not a fad. People who realize the value of health make it a priority, keeping records of all their fitness-related facts. In this article, we’ll examine the design of the database behind a health and fitness application.
There are many applications which let users log their health and fitness information. A couple of big players like Apple, Google, and Microsoft have launched their own development APIs specifically for this market.
A Process Management Data Model
What kind of data model can handle all the planning and activities used in process management? In this article, we discuss one design for a process management database.
Process management is a fairly straightforward and common concept. At its core, process management is simply deciding what needs to be accomplished – building a car or creating an app, for example – and then figuring out how to do it. Of course, the actual process itself is more complicated!
Improving Our Online Job Portal Data Model
In this era of tough competition, job portals are not just platforms for publishing and finding jobs. They are leveraging advanced services and features to keep their customers engaged. Let’s dive into some advanced features and build a data model that can handle them.
I explained the basic features needed for a job portal website in a previous article. The model is shown below. We’ll consider this model as a base, which we will change to meet the new requirements.
The Proper Way to Handle Multiple Time Zones in MySQL
In this article, I’ll walk you through some fundamental considerations for working with date- and time-related data in MySQL. We’ll also look at how to handle multiple time zones and daylight saving time changes. Let’s first address some core concepts that will help us understand the underlying complexity of time-related data. It is important to notice that these concepts apply when representing a point in time rather than an absolute duration.
A look at algorithms used in RDBMS implementations of DWH systems
When you’re using a data warehouse, some actions get repeated over and over. We will take a look at four common algorithms used to deal with these situations. Most DWH (data warehouse) systems today are on a RDBMS (relational database management system) platform. These databases (Oracle, DB2, or Microsoft SQL Server) are widely used, easy to work with, and mature – a very important thing to bear in mind when choosing a platform.
Again and Again! Managing Recurring Events In a Data Model
A recurring event, by definition, is an event that recurs at an interval; it’s also called a periodic event. There are many applications which allow their users to setup recurring events. How does a database system manage recurring events? In this article, we’ll explore one way that they are handled.
Recurrence is not easy for applications to deal with. It can become a hurricane task, especially when it comes to covering every possible recurring scenario – including creating bi-weekly or quarterly events or allowing the rescheduling of all future event instances.
Database Modeling Tips
When you were learning database concepts, data modeling looked pretty easy, didn’t it? You knew all the rules, and modeling seemed like a game: get a challenge, do your best, and eventually solve it. Job well done! Moving up to the next level – and so on. As you continue, though, you’ll see that database modeling is also an art. Many cases require a totally new approach. Everything can be done ‘by the book’, but sometimes you get better results when you go less orthodox.
The Reference Data Pattern: Extensible and Flexible
Having reference tables in your database is no big deal, right? You just need to tie a code or ID with a description for each reference type. But what if you literally have dozens and dozens of reference tables? Is there an alternative to the one-table-per-type approach? Read on to discover a generic and extensible database design for handling all your reference data.
This unusual-looking diagram is a bird’s-eye view of a logical data model (LDM) containing all the reference types for an enterprise system.
Tip #5 – How to quote table names in generated SQL script
Sometimes, you want all identifiers in your SQL script to be quoted. For example, you want them to have a particular case. Luckily, Vertabelo allows you to quote all SQL identifiers in your generated script to prevent you from any SQL name conflicts.
Go to your model:
Click the SQL generation settings tab in the Model properties panel on the right:
Check Quote all SQL identifiers:
Interesting Changes in MySQL 5.7
The General Availability version of MySQL is still version 5.6, but the development release of MySQL 5.7 definitely introduces some exciting changes to the world of database management systems. Is it worth giving a try? In this article, we’ll have a closer look at a few brand-new features that may help you decide to do so.
Native JSON Support JSON (short for Java Script Object Notation) is a format for storing information which can be a good alternative to XML.
What Is a Database Index?
Sooner or later there comes a moment when database performance is no longer satisfactory. One of the very first things you should turn to when that happens is database indexing. This article will give you a general overview on what indexes are without digging into too much detail. We’ll discuss additional database index topics in future articles.
In general, a database index is a data structure used to improve queries execution time.
SQL Performance Explained – the must-read book
Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013. Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled “Indexes: The neglected performance all-rounder.” Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought.
Designing a Database: Should a Primary Key Be Natural or Surrogate?
Suppose we design a database. We’ve created some tables, each one has a few columns. Now we need to choose columns to be primary keys (PK) and make references between tables. And here some inexperienced designers face the dilemma – should a primary key be natural or surrogate?
There’s one and only one answer to that question: it depends. If anyone ever tried to convince you that you should have only natural keys or only surrogate keys, just smile :)
