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.
775 questions
5
votes
3
answers
235
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, ...
- 169
2
votes
2
answers
953
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
177
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
3
votes
4
answers
527
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 ...
- 83k
0
votes
1
answer
92
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 ...
11
votes
13
answers
4k
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
176
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 ...
- 841
2
votes
1
answer
155
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
112
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
424
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
937
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
-2
votes
2
answers
120
views
Identifying the minimal set of columns that can be used to define a unique instance in a database table
Some context here:
I am developing a comparison tool that coalesces, formats and concatenates all column values for the individual rows in a table. This single string is further reduced in size by ...
- 1
0
votes
3
answers
137
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
vote
2
answers
130
views
Database per Tenant using SQL Server [closed]
I am looking to create an application which will have following architecture:
Site Database (Which will run on-prem or cloud). If multiple sites then multiple database instances for each site. In ...
-1
votes
1
answer
206
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
2
votes
3
answers
621
views
Dynamically transform EAV data into standard SQL table
I use MariaDB to store data using EAV model. I need EAV, because each attribute of each entity can have its own validity (validFrom, validTo).
For example, I have entity person and I track changes ...
- 277
0
votes
1
answer
89
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
2
votes
2
answers
162
views
Do we have 2 logical query processings, one with indexes and one without indexes?
In the book "Inside Microsoft® SQL Server® 2008: T-SQL Programming" the behaviour of a sql query is explained. The following picture is taken from the book. I have some questions about the ...
- 29
3
votes
1
answer
140
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
257
views
How to organize "master" data VS "working" data in MS SQL
I have this survey software that I'm writing and I'm wondering what would be the best design for my requirement.
I'm going to simplify it as best as I can.
I have these entities:
class Survey {
...
- 101
0
votes
1
answer
329
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
2
votes
2
answers
214
views
Do RESTful endpoints elminate the need to SQL triggers?
Do RESTful endpoints eliminate the need to SQL triggers?
Triggers are supposed to execute after a certain event. Would moving to RESTful api endpoints conceptually eliminate the need for SQL triggers?
...
- 149
0
votes
1
answer
68
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 ...
15
votes
9
answers
4k
views
Does 3-valued logic ever provide practical benefits over 2-valued logic?
I was looking at an SQL query recently and found what I think is likely a bug. It was related to case statements on inequalities. I was trying to replace it with a min/max type alternate and when ...
- 28.9k
5
votes
1
answer
247
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> ...
- 257
0
votes
6
answers
1k
views
Is it best practice for each call to a SQL Server to be in its own class, even when using dependency injection?
I often see/write classes that contain every part of the application where the application will talk to a SQL server. For example, you may have a class like this
public class Data
{
private Func&...
- 1,037
3
votes
7
answers
473
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,037
0
votes
0
answers
196
views
SQL Database design for reusable components and classes
I am working on a small suite of enterprise applications, and I am trying to determine the best way to make them more consistent and maintainable across the board. The applications are .Net Blazer ...
- 111
26
votes
8
answers
10k
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
0
answers
102
views
Data producers and consumers: How to connect MySQL with microservices?
Consider a web-app with 5 micro-services deployed with Docker, and a MySQL container dedicated to storing data produced from other services (shared-database pattern). How should I make the connection ...
- 125
0
votes
3
answers
106
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) (...
- 165
5
votes
5
answers
5k
views
How can I protect an SQL connection string in a client-side application?
I am developing a .NET Windows application and I need to make requests to a SQL Server instance. How do I secure the authentication data in my code in case someone decompiles my application?
I know ...
- 85
-2
votes
1
answer
631
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
45
votes
5
answers
7k
views
SQL sanitizing in code with no user input
In my company's codebase, we hardcode sql queries without using an ORM.
Here's an example of a query we would run:
UPDATE client SET status="active" WHERE client_id=123
Since the query is ...
1
vote
1
answer
4k
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 (...
- 65
3
votes
2
answers
1k
views
Is it a good practice to have two unique IDs for an SQL-Database?
Currently I want to create a SQL database effectively and "logically". Lately I read a lot about the issues that it is (mostly) not a good idea to define primary keys that can be seen from &...
- 65
2
votes
2
answers
338
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
15
votes
9
answers
10k
views
Can it be acceptable to construct SQL queries dynamically?
I know that as a general rule, you shouldn't construct SQL queries dynamically because of the possibility of SQL injection.
However, it could come in quite handy to break this rule and define for ...
- 185
1
vote
1
answer
238
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 ...
- 191
0
votes
2
answers
1k
views
Is it okay to open separate database connections in each method or is there a better way to do it?
I have a static class in my code that has two methods that store different data in a SQL database when they are called. In each of the methods I open a connection to the database. Is this the proper ...
- 535
-2
votes
1
answer
136
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
13
votes
10
answers
8k
views
Is there any benefit to a separate table that is one-to-one with the primary data table?
I've inherited a system with an Oracle relational database with a couple of tables modeled like I've sketched below, where there's an entire child table that only stores a single status code in a one-...
- 386
1
vote
0
answers
130
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
0
votes
1
answer
94
views
Am I making this web app right?
I am currently making a newsletter website that will send you a email with the top ten manga for that week. Currently I have a web scraper that retrieves the top ten along with descriptions and other ...
1
vote
0
answers
40
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
-2
votes
2
answers
177
views
How to filter and concatenate multiple sql files into one database [closed]
I have an issue where I have multiple databases from previous projects that I would like to combine into one large database. These databases are stored in .sql files. The issue is that I only need ...
- 7
1
vote
4
answers
3k
views
What are the use cases for SQL row version columns?
Docs and blog posts describe what row version columns do, but rarely delve into the decision process of when it's appropriate to use them. I suspect that many developers just add them to every table ...
- 2,596
1
vote
1
answer
441
views
How to represent NoSQL entities
I've mainly studied SQL structures, and I know that the representation of the entities can look something like this:
However, I'm building this app with MongoDB, so NoSQL DB. I'm aware that MongoDB ...
- 13
18
votes
3
answers
9k
views
Are surrogate keys a known anti-pattern?
My workplace's database has a pattern that I've not seen before. Every column that is intended to be a key, whether primary or foreign, ends in _SK. This is shorthand for "surrogate key". It ...
- 1,037
-3
votes
1
answer
112
views
Where should linking tables be stored? [closed]
I have a system where properties can be stored, and linked with multiple other entities (each with their own schema). Let's say that Documents can be linked with Users and Assets. What is the best ...
- 181