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.
769
questions
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 ...
- 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 ...
- 41
1
vote
1
answer
95
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
388
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
640
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
107
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
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 ...
- 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 ...
-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 ...
- 111
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 ...
- 277
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 ...
- 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
134
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
217
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
283
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
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?
...
- 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 ...
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.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> ...
- 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&...
- 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 ...
- 1,017
0
votes
0
answers
181
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
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 ...
- 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) (...
- 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 ...
- 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 ...
- 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
333
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
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 ...
- 185
1
vote
1
answer
196
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 ...
- 189
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 ...
- 525
-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 ...
- 157
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-...
- 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" ...
- 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
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 ...
- 155
-2
votes
2
answers
163
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
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 ...
- 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 ...
- 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 ...
- 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 ...
- 103
-1
votes
2
answers
201
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 ...
- 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 ...
- 599
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 ...
- 471
-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 ...
- 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 ...
- 337