Skip to main content

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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
123 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 ...
AgostinoX's user avatar
  • 841
2 votes
1 answer
153 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 ...
Sergey K's user avatar
1 vote
1 answer
97 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 ...
Zé Pinhao's user avatar
3 votes
4 answers
390 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 ...
cargt3's user avatar
  • 39
1 vote
3 answers
649 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 ...
Jeremy Fisher's user avatar
-2 votes
2 answers
108 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 ...
Lauren_G's user avatar
0 votes
3 answers
117 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 ...
flo's user avatar
  • 1
1 vote
2 answers
122 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 ...
ProDBdeveloper's user avatar
-1 votes
1 answer
203 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 ...
HappilyCoding's user avatar
2 votes
3 answers
538 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 ...
user14967413's user avatar
0 votes
1 answer
79 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 ...
TIKSN's user avatar
  • 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 ...
jwa's user avatar
  • 29
3 votes
1 answer
135 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 ...
javery's user avatar
  • 61
0 votes
1 answer
218 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 { ...
Francis Ducharme's user avatar
0 votes
1 answer
284 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 ...
Dolotboy's user avatar
  • 111
2 votes
2 answers
203 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? ...
henhen's user avatar
  • 149
0 votes
1 answer
65 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 ...
Kellen Cataldo's user avatar
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 ...
JimmyJames's user avatar
  • 28.5k
3 votes
0 answers
116 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> ...
Alexey's user avatar
  • 237
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&...
J. Mini's user avatar
  • 1,017
3 votes
7 answers
437 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 ...
J. Mini's user avatar
  • 1,017
0 votes
0 answers
183 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 ...
aterbo's user avatar
  • 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; ...
Lenny Zhou's user avatar
0 votes
0 answers
96 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 ...
JrCaspian's user avatar
  • 125
0 votes
3 answers
104 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) (...
Mr. TA's user avatar
  • 155
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 ...
Motti's user avatar
  • 85
-2 votes
1 answer
559 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 ...
ALZ's user avatar
  • 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 ...
half of a glazier's user avatar
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 (...
Vito's user avatar
  • 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 &...
Vito's user avatar
  • 65
2 votes
2 answers
334 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 ...
Ivaylo Toskov's user avatar
15 votes
9 answers
9k 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 ...
Thomas's user avatar
  • 185
1 vote
1 answer
198 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 ...
Leftover Salad's user avatar
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 ...
BeeFriedman's user avatar
-2 votes
1 answer
127 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 ...
geofflittle's user avatar
11 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-...
Kaypro II's user avatar
  • 366
1 vote
0 answers
128 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" ...
user2563087's user avatar
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 ...
moosepowa's user avatar
1 vote
0 answers
39 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 ...
Pierre-Alexandre's user avatar
-2 votes
2 answers
165 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 ...
ste's user avatar
  • 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 ...
Kevin Krumwiede's user avatar
1 vote
1 answer
403 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 ...
fel.jim's user avatar
  • 13
18 votes
3 answers
8k 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 ...
J. Mini's user avatar
  • 1,017
-3 votes
1 answer
107 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 ...
mft25's user avatar
  • 171
0 votes
1 answer
70 views

How to handle data isolation in one DB when handling SQL queries as user input in Rails

I have a task to develop a website that allows storing of data resources from other APIs and users can analyse and visualize this data. This data is sensitive and data leakage should be minimal. I ...
axelmukwena's user avatar
-1 votes
2 answers
202 views

Difficulty understanding how denormalization results in more storage necessary

I am having difficulty understanding how denormalization results in more storage necessary. For example, let's say that in a normalized relation, there's Table 1 and Table 2. In order to join Table 1 ...
Henry Zhu's user avatar
  • 153
1 vote
2 answers
78 views

How to write robust or alternative to TSQL data processing routines in SqlServer?

My company has a workflow that involves ingesting huge amounts of raw data from external sources into SqlServer and then interpreting and weaving that data into our own application tables. This ...
Joshua Frank's user avatar
1 vote
3 answers
1k views

Storing count of child rows in parent table

I have two tables, parent and child. Parents can "have" multiple children—in our case they are related through a third table, parent_child_mapping rather than the child storing its parent id ...
Andrew Cheong's user avatar
-3 votes
1 answer
342 views

How much access should a developer have to a Staging database [closed]

We want our developers to be able to diagnose production issues in staging. For the sake of this, we are planning to make an exact replica of production into staging. We are thinking however, if we ...
James's user avatar
  • 109
-2 votes
1 answer
99 views

Should cached data be kept separate from primary tables?

I have a table in an SQL database for storing information about some business object that looks something like this (details changed so as not to give away what company I work for): CREATE TABLE ...
Omegastick's user avatar

1
2 3 4 5
16