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
128
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 ...
- 69
1
vote
1
answer
279
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
6
votes
3
answers
326
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
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
0
votes
1
answer
119
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
0
votes
1
answer
401
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
1
vote
1
answer
148
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
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
0
votes
3
answers
186
views
Python API - store data in SQL AND NoSql
I am a student and am currently programming an API in Python. Among other things, it is possible to register, log in, create a user profile with data, etc.
I would like to be able to store and ...
- 1
-1
votes
1
answer
228
views
Modeling a CSV file: What is the standard? Python or SQL?
I have a wide CSV file of about 350mb, and want to load it into a SQL database and properly model the data to make it easier to use for analysis.
I could split the data into tables with python and ...
- 111
0
votes
1
answer
131
views
How to design a region table for localization standardizations with multiple criteria
I am designing a database for localization standardizations.
It contains Languages, Regions, Cultures, etc.
For the region, I have a hierarchical structure that contains a name and a parent which is a ...
- 109
3
votes
1
answer
194
views
Strategies for Adapting SQL-Based Data Retrieval to a Newly Introduced REST API in a Short Timeline
I'm in a pickle following a recent executive decision by our parent company. They have elected to abstract away our SQL data warehouse, transitioning to a REST API for data retrieval. The purported ...
- 61
0
votes
1
answer
537
views
How can I trace back the original table of a column?
New to a software engeneer job and I am kinda blocked on the way to go with my new project
To explain this in a scheme.
I have a source of data in a custom app that is for most part of the time the ...
- 111
0
votes
1
answer
78
views
How to handle concurrently caching expensive request data in postgres?
I have a kubernetes deployment which is fielding expensive (but cache-able) requests, let's say a website scraping service (not really) which takes about 15 seconds to scrape a website. In my backend ...
7
votes
1
answer
336
views
Why is the highest NUMERIC precision in most RDBMS 38?
SQL-92 says:
16)For the <exact numeric type>s DECIMAL and NUMERIC:
a) The maximum value of <precision> is implementation-defined.
<precision> ...
- 277
4
votes
7
answers
616
views
How can unit tests be atomic in server-dependent CRUD apps?
Suppose that the following are true:
You believe that unit tests should be atomic. That is, tests should always test exactly one thing.
You have written a CRUD app in a general-purpose language such ...
- 1,015
26
votes
8
answers
11k
views
Is it okay to hard-code table and column names in queries?
I've got a backend running on Node that executes queries on a PostgreSQL database. For these queries, table and column names are imported from a .env file, for example:
const ID = process.env.ID_COL;
...
- 371
0
votes
3
answers
132
views
How to avoid data corruption with dual parent/child foreign keys
Imagine the following:
Persons table: (Id, FirstName, LastName)
PersonEmails table: (Id, PersonId, Address) (to allow a person to have multiple emails)
Contacts table: (Id, PersonId, UnsubscribeAll) (...
- 175
-2
votes
1
answer
851
views
Adding new column(s) to DB Table breaks existing queries [closed]
Dilemma: I am working in microservices (MS) architecture for a product with shared (PostgreSQL) DB between MSes and DB Views exposed as Data Access API between SW Components, written and maintained by ...
- 107
1
vote
1
answer
5k
views
SQL or NoSQL Database for a chat application?
currently I am working on a kind of "Chat" Application.
The app consists of "threads". Each of this threads consist of "subthreads" in wich a user can send a message (...
- 75
2
votes
2
answers
448
views
How to store queryable 10-100MB BLOBs?
I have read several discussions about storing BLOBs in the database vs in an object storage. What I need in addition though is a functionality for querying these BLOBs. The BLOBs will be immutable ...
- 129
1
vote
1
answer
322
views
SQL - store "like" counts as separate column or infer from a query?
Say I have a feature in a web app where users can create a post and like it. In the frontend the user should see the number of likes a post has.
I could store the data two ways:
1. Option 1: A small ...
- 249
-2
votes
1
answer
170
views
System design to present live query results over realtime data
Question
What is a good storage layer, coding paradigm, and query language for computing over realtime data?
Use-case
For example, stock and options prices are (essentially) realtime data streams. I ...
- 157
1
vote
0
answers
154
views
Efficient way to implement hierarchical inheritance in SQL?
I am working in a database that has a hierarchy of Companies (From location-level all the way up to Top-level parent company). I am designing a system within that to provide a "subscription" ...
- 11
1
vote
0
answers
48
views
Storing SQL for table creation
I am currently working on a small web-app stored in a monorepo and using PSQL as database (hosted on GCP). It's my first time working for a web app and I have to create a lot of tables manually such ...
- 165