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
-1 votes
0 answers
49 views

Is using stored procedures for everything a known anti-pattern? [closed]

I'm currently working on a very strange database. Its primary use is using ABO.NET to import data from a SQL server to VBA-heavy Excel spreadsheets. I've spotted a very weird thing in this database; ...
  • 361
0 votes
1 answer
74 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
35 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
76 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
0 votes
3 answers
100 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
120 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
7k 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 ...
  • 361
0 votes
0 answers
97 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
79 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 ...
  • 151
0 votes
1 answer
56 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
87 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
53 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
152 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
130 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
57 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
119 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
461 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
125 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
197 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
66 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
48 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
0 votes
1 answer
124 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
83 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
102 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
23 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
59 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
84 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 ...
3 votes
3 answers
1k 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
172 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 ...
0 votes
1 answer
81 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 ...
3 votes
3 answers
511 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 ...
  • 251
0 votes
2 answers
212 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
115 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
74 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
198 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 ...
0 votes
2 answers
49 views

Handling a column in a database which can change on external conditions

Let's suppose we have a table of products, similar to this one: ProductId ProductFriendlyName ProductPrice 84 Coke can 2 When a specific condition applies (the actual condition isn't really ...
  • 59
0 votes
1 answer
160 views

Variable WHERE clause while avoiding sql injection

I want the user to be able to provide a custom WHERE statement against a mysql-backed platform... do I need to worry about any possible SQL injection attacks notwithstanding invalid sql? See the ...
4 votes
2 answers
2k views

Alternative To Generic Methods where Type is known at runtime

I've written a class that synchronizes a table between two databases using Dapper. The public and private Methods in the class are generic and the generic parameter is the POCO class that is being ...
  • 379
0 votes
1 answer
115 views

quality management on SQL stored procedures

I've always developed my own applications and use certain criteria to evaluate if the code is acceptable from a quality perspective. Now I've switched jobs and have to among other things maintain ...
1 vote
1 answer
169 views

Synchronizing Multiple Local SQL databases with Server

I'm trying to understand what general mechanisms and/or concepts are available in SQL database last to synchronize local. Here are my inputs and requirements. Multiple clients have a full copy of the ...
  • 379
0 votes
0 answers
79 views

What is the best practice for hierarchical container - item relationship in a SQL database?

I am in the process of designing a database organizing real world items in different storage locations. One main principle is that items can contain other items. So the relevant table is: An "...
4 votes
2 answers
147 views

How should my application treat database collisions that are handled safely?

My applications uses error reporting to track exceptions. My application uses SQL transactions to make sure that operations are completed completely or not at all. My application is multi-threaded. ...
  • 149
3 votes
0 answers
149 views

Relational or non-relational database for storing and querying log data?

I'm planning to extend an application with logging of user events. (Client application via REST-API to Symfony). What is the preferred type of databases? Currently the log data is stored in a ...
0 votes
0 answers
184 views

Storing tree data in a database for fast reading of the whole hierarchy

I have a class that looks something like this: class Entity { public string Id { get; set; } public List<Entity> Children { get; set; } public void DoStuff(Data data) { ...
  • 109
0 votes
1 answer
122 views

Subdomain and Database per customer?

I'm currently working on a farming IoT system and confused about the architecture so I wanted to get your views. I'm using a relational DB (PosgreSQL). I have multiple customers, each customer has one ...
  • 119
-1 votes
1 answer
170 views

What advantage is gained by continuing to not provide an UPSERT statement in T-SQL (or any given major SQL dialect)?

This question is asking about a generally applicable engineering principle. It is using T-SQL as a specific example, but the question is about the engineering principles behind major SQL dialects in ...
  • 3,085

1
2 3 4 5
15