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.
745
questions
5
votes
5
answers
3k
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 ...
- 83
-2
votes
1
answer
90
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
130
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
155
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
253
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
7k
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
65
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 ...
- 153
0
votes
2
answers
321
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 ...
- 345
-2
votes
1
answer
46
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
9
votes
10
answers
4k
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-...
- 346
1
vote
0
answers
74
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
88
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
37
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 ...
- 137
-2
votes
2
answers
93
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
371
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,576
1
vote
1
answer
162
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,071
0
votes
0
answers
162
views
Using NoSQL (vs SQL) Database for Metadata in a highly scalable System like Facebook
If you are required to build a highly scalable system like Facebook would you prefer to use NoSQL database or a SQL database for the metadata.
I would use a SQL database for metadata i.e User Info, ...
-3
votes
1
answer
87
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
58
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
93
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
62
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
293
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
170
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 ...
- 9
-2
votes
1
answer
67
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 ...
- 318
-2
votes
1
answer
126
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 ...
0
votes
4
answers
805
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 ...
- 75
-3
votes
1
answer
128
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
463
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
86
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
49
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
228
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
84
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
0
votes
1
answer
108
views
Syncing and matching local records to external database that behaves like a black hole
Black hole problem
I am working on matching and daily syncing of our internal data records to an external database that behaves like a black hole, meaning, we cannot view nor extract any data from it ...
- 9
0
votes
0
answers
24
views
Modelling DropDowns in ef core
Im working on an application which essentially fills a giant government/court form.
Lets say i have an an Entity Unemployment and Unemployment has a ... thing "Reason why" and there are 6 ...
0
votes
1
answer
63
views
Table design question - DynamoDB the right choice?
Our team has started to use AWS and one of our projects will require storing approval statuses of various recommendations in a table.
There are various things that identify a single recommendation, ...
- 101
0
votes
1
answer
70
views
Finding/determining future data for pricing
I'm honestly not even sure how to go about how to ask what I'm looking for/need. lol.
I'm currently using Sql Server for my database and the 'new' .net 5. There are multiple pricing tables that each ...
- 19
0
votes
0
answers
147
views
How to implement notification system based on time that runs in regular intervals
I have a MySQL database with nodeJS server. I want to implement a notification functionality which is as follows. Once a user registers, I want to (inform) send some particular information to the user ...
4
votes
3
answers
2k
views
SQL: enum vs reference table when values are referenced in the code
This question considers whether SQL DB data should be represented as an enum or by a reference table, when the application code is aware of particular instances of said data.
I'll illustrate with an ...
- 41
1
vote
1
answer
228
views
SQL for storing hierarchical data
I have a UX that is modeling a hierarchy. There is a concept of a section, a sub-section under a section, a panel under a section and a bunch of form-fields under a panel.
The UX exposes an ability to ...
- 119
2
votes
0
answers
36
views
Populating form options in dynamic web form
I am looking for advice on database design for a web app I am building.
In the web app (Angular, REST API, Oracle SQL database), I am constructing a web form dynamically. I chose dynamic form ...
- 41
2
votes
2
answers
133
views
Displaying various subsets of content stored in a database
I am looking for advice on database design for a web app I am building.
Part of the website needs to display content from a SQL database dynamically depending on the component's context. To be more ...
- 41
4
votes
3
answers
820
views
What benefit does a junction table provide over adding an additional field with a foreign key?
Consider this simple example:
We have the apartments table below:
ID
Address
0
555 Maple St
1
123 Oak St
2
999 Pine St
We also have the tenants table below:
ID
Name
0
Bob Foo
1
Jane Bar
If I ...
- 261
1
vote
2
answers
314
views
How to manage primary keys in CQRS
I'm building a backend following the CQRS pattern and I don't know how to manage properly the primary keys (surrogate keys) between the command and query databases.
For example: I have a model with ...
-3
votes
1
answer
107
views
How do I evaluate if a data warehouse is necessary?
I need to put data from MongoDB together with tracking data from a quiz app (stuff like which users clicked what button etc). Then I’ll use this in machine learning.
I looked at all the DW options ...
- 13
0
votes
1
answer
167
views
Reactjs with large DB
I am working with a very large SQL database through a frontend reactjs application with a node server intermediary using an ORM for query sanitation. However due to the size of the DB it takes some ...
0
votes
2
answers
79
views
Best practices when designing SQL DB with "redundant" tables
I have a design dilemma for a DB I'm creating for an e-commerce platform I want to develop.
I have 3 different actors interacting with the website:
Customer
Manager
Supplier
Those 3 actors have the ...
- 569
-3
votes
1
answer
294
views
How to simulate test data to a database?
I've created a service application that import orders from an SQL database. Right now I'm manually entering the data into the database, but in release a 3rd party application will export orders to the ...
- 129