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
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 ...
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 ...
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 ...
-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 ...
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" ...
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 ...
-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 ...
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 ...
-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 ...
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 ...
-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 ...
-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 ...
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 ...
-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 ...
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, ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...

1
2 3 4 5
15