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.
760
questions
2
votes
2
answers
99
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 ...
- 137
0
votes
1
answer
41
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
1
vote
2
answers
154
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 ...
- 19
3
votes
1
answer
118
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 ...
- 33
0
votes
1
answer
102
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
138
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
157
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
59
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 ...
- 25.9k
3
votes
0
answers
78
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
784
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,007
3
votes
7
answers
345
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,007
0
votes
0
answers
109
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
9k
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
63
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
98
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
4k
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
191
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
6k
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 ...
0
votes
1
answer
2k
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 (...
- 55
3
votes
2
answers
553
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 &...
- 55
2
votes
2
answers
280
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
8k
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
81
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 ...
- 179
0
votes
2
answers
798
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 ...
- 355
-2
votes
1
answer
51
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
5k
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
100
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
91
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
38
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
112
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
1k
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
270
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,007
-3
votes
1
answer
91
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 ...
- 161
0
votes
1
answer
65
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
148
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
72
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
703
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 ...
- 425
-3
votes
1
answer
273
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
72
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 ...
- 317
-2
votes
1
answer
131
views
What is the right approach for dynamically creation of SQL-queries?
The problem:
I have a application where the data comes from SQL tables. The design of the tables differs a bit from client to client. Example:
ClientA sells thermometers and the product has features ...
1
vote
4
answers
1k
views
HOWTO: Full-text search over an encrypted database?
I want to adhere to the best practices and obviously encrypt the data sent to me by user. And I also want to provide the full-text search feature to the user (user can only search their own data, if ...
- 85
-3
votes
1
answer
145
views
Most efficient way to get average data for thousands of records from the database [closed]
I have a database that keeps a temperature readouts, which are being added - let's say - every minute:
+----+---------------------+-------------+-----------+
| id | timestamp | temperature | ...
- 149
37
votes
13
answers
8k
views
Are database unique indexes a mask on bad scripting?
I am working with a coworker on a project that uses Inductive Automation software. If you don't know what it is, all you need to know is it provides a drag-and-drop GUI designer (based in java swing) ...
0
votes
1
answer
1k
views
Application settings in relational database
There is an application of ours which has some experimental features. We want to enable it when user wants to enable. So they will change it in the UI and we are saving those to DB.
So we added those ...
- 100
1
vote
2
answers
121
views
Compare diff between similar objects stored in two different databases and delete the missing records from the later
We have a legacy system (app 1) that has millions of records of an object Project. It has ProjectId and OrganizationId columns along with other properties. A recently added service (app 2) gets the ...
- 19
2
votes
1
answer
52
views
SQL: Analytics event value that can be string or number
I'm using a PostgreSQL database and I want to create an analytics table that has a many-to-one relationship with an analytics_type table where analytics_type_id is the foreign key. The table also has ...
- 47
1
vote
1
answer
408
views
SQL Foreign Key Relationships and External Data
I work for a company that wants an external system built outside of their core system. I can't share an SQL database, and can't store data in their database. So I am forced to maintain my own SQL ...
- 127
-1
votes
1
answer
100
views
Commit instead of rollback? [closed]
Simple example (PSEUDO CODE):
for (int i = 0; i < 100; i++) {
START TRANSACTION;
SELECT id, name FROM employees WHERE id = i;
IF (someFunction(id)) {
ROLLBACK;
...
- 11