Questions tagged [sql]
Structured Query Language (SQL) is a language for managing data in relational database management systems. This tag is for general SQL programming questions; it is not for Microsoft SQL Server (for this, use the sql-server tag), nor does it refer to specific dialects of SQL on its own.
779 questions
1
vote
1
answer
135
views
Where should authentication data be stored in a modular monolith with multiple authentication strategies? (NestJS)
I’m implementing the authentication module in a modular monolith built with NestJS.
Initial Draft
In the first draft, the User entity included the hashed password, refresh token, and refresh token ...
- 104
1
vote
2
answers
251
views
How does a PostgreSQL cursor actually work? What are their tradeoffs?
I'm in charge of developing an application that sometimes needs to process massive amounts of data from a Greenplum (a PostgreSQL-derived) database. The process involves a Java 8 program running on a ...
- 31
1
vote
1
answer
281
views
Does SQL Server Agent predate Windows Task Scheduler?
An old guru once told me that SQL Server Agent was created because Windows Task Scheduler did not exist at the time. However, all of my research shows that they both released in 1995. For Task ...
- 1,015
0
votes
2
answers
194
views
How to minimize the issues when SQL PreparedStatement is not an option
The application in question is integrating with BigImportantThing (BIT). Part of our application's job is to provide a better interface, so this is something to resolve within our application. The ...
- 109
6
votes
3
answers
330
views
Quality Assurance for Large SQL Script Releases
I'm often releasing large SQL scripts for projects and minor works - my problem is that there's nothing (except the logs) to indicate that the release was successful. There could be an object missing, ...
- 179
2
votes
2
answers
1k
views
How can I manage validation logic for 150+ screens with unique business rules across microservices?
Currently, I'm working on an application with a microservice architecture. Each screen may corresponds to a separate microservice, and each screen has its own unique validation logic. Some screens ...
2
votes
1
answer
1k
views
One and only one vs One in crow's foot notation
Am I using one and only one correctly? I have read so many articles online on differences between one and only one vs one and I'm still confused.
I.e.
a customer can exist with zero, 1 or many (...
- 29
5
votes
5
answers
772
views
How does data store compression speed up data warehouses?
I often see the claim that various data warehouse/analytical database systems derive significant performance benefits from compressing their data stores. On the face of it, though, this seems to be ...
- 83.3k
0
votes
1
answer
121
views
Database structure for two-step registration flow
I'm trying to design a database for supporting a multi-step registration flow. The registration flow goes like this: the user logs in via OAuth (which creates a session and user), then they're asked ...
- 123
11
votes
13
answers
7k
views
Does it ever make sense to have a one-to-one obligatory relationship in a relational database?
To illustrate what I mean, imagine a group of students and a group of professors in some kind of a traineeship. Every professor is to mentor one student and every student has to be mentored by one and ...
- 121
0
votes
1
answer
409
views
What is the purpose of setting an isolation level for an INSERT INTO statement?
I want to better understand how isolation levels work, and here is my current understanding:
Isolation levels determine how a transaction is isolated from concurrent transactions. They are typically ...
- 849
2
votes
1
answer
180
views
How to design sharing feature properly in DB?
My DB has three tables:
CREATE TABLE Users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE Categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
user_id INTEGER ...
- 41
1
vote
1
answer
149
views
Single-source data warehouse permissions management
We (Data Platform team) are reviewing how we configure and apply permissions against our data warehouse objects, and I'm curious what tools or custom systems you might be using for this.
For context ...
- 21
3
votes
4
answers
517
views
Encryption of PII data in database logic in store procedures and desktop application [closed]
I have an old desktop application (Delphi) with logic in stored procedures (Oracle). The application is storing some personal data including salaries. I want to encrypt data so someone with db access ...
- 39
1
vote
3
answers
2k
views
What is the correct way to find the differences between 2 relational tables?
Typically the solution for comparing if 2 relational db tables (I am using AWS Athena) are equal is to do full outer join on all the columns but adding an extra column to each dataset that acts as a ...
- 345