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.

47 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
2k views

The best way to convert Dynamic SQL to C#/Linq while joining multiple tables

I am trying to find the best way to convert the following dynamic SQL snippet to C# / Linq. The question is complicated by the use of multiple tables within the dynamic SQL. I've examined the ...
4
votes
1answer
345 views

Preferred approach to a matching process

I am currently in the process of putting together a matching algorithm. The matching process is as follows: Query data is used to perform a "lookup" on a set of reference data in order to determine ...
3
votes
0answers
130 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 ...
3
votes
0answers
260 views

How to model access rights in MongoDB versus SQL

I've started to implement a full MEAN-stack application (MongoDB, Express, Angular2, node) and I am struggling with the data design for the access management in the MongoDB. In my old thinking tracks, ...
3
votes
0answers
499 views

ODBC 3 Multiple Statements vs Multiple Connections

So right now I have a single thread to handle all the requests for the database. Let's say I have 400 requests per second for logins / logouts / other stuff, and 400 requests per second which are only ...
3
votes
0answers
357 views

Match buyers and sellers based on date ranges and quantity of shares

NOTE: This is in reference to the question I asked on Stack Overflow but was told to post this here I'm building a trading application that matches buyers/sellers that own subscription. Subscriptions ...
2
votes
0answers
28 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
0answers
69 views

SQL - Locating closest value in a set

I have a table that contains the percentiles from a reference data set. CREATE TABLE [Core].[RiskFactor]( [SpecialtyCode] [char](2) NOT NULL, [RiskCategoryId] [int] NOT NULL, [Percentile] [tinyint] ...
1
vote
2answers
38 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 ...
1
vote
1answer
72 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 ...
1
vote
2answers
145 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 ...
1
vote
0answers
62 views

Issues Regarding Data Model Abstraction in Golang

I am currently writing a quiz web-app with a React front-end and a Golang backend, with a PostgreSQL database to keep track of the quizzes, the questions on the quizzes, and the choices in the ...
1
vote
0answers
118 views

Should I decouple MySQL database?

I have two sites, one is a subdomain of the other. They both do different things entirely with one unifying goal. My intent is to have the main site do a majority of the heavy lifting, and the ...
1
vote
0answers
90 views

Paginate large data and store in temporary table for navigation

I have implemented pagination using SQL and stored that result of search into temporary table. Temporary table is named after unique tab id. So that other tab can have seperate result sets. That ...
1
vote
0answers
34 views

Partial database synchronisation across multiple databases

Currently we are looking for a solution to sync databases across multiple locations. We a location hierarchy such as Country -> State -> District -> Center. To increase speed and reliability of the ...
1
vote
2answers
299 views

Database Model Classes in TypeScript

I'm using Knex.js with TypeScript for database access. Table uid (UUID, auto-generated by Postgres) name (varchar) Model interface User { uid: string | null; name: string; }...
1
vote
0answers
34 views

What approach can be used to automate SQL views generation?

Let's say I have to create tons of different SQL views. Having actual and target table DDLs, is it possible to programmatically automate view creation? Example: I have 2 table DDLs: CREATE TABLE ...
1
vote
0answers
598 views

How to Investigate the difference between query execution times?

I have a simple query, when executed directly in SQL Developer Studio (It's an Oracle Database) takes around 4 seconds. But when executed via my Java code it takes around 20 seconds. I'm using ...
1
vote
0answers
131 views

Creating an autoupdate for my program and its database

I've already an offline updater as a download but I want to make it automatically. It's a client/server software and a ms sql database. 3 parts have to be updated: update of a proprietary software (...
1
vote
0answers
84 views

How to deal with requests that don't conform to the existing data structure?

The system I'm working on has a list of assets that belong to different customers of the company. This data is saved on an SQL database. Removing irrelevant data, let's say the table is like this: +-...
1
vote
0answers
266 views

An algorithm to implement SQL's ORDER BY with TOP/LIMIT or OFFSET/FETCH

Is there a general algorithm to implement SQL's ORDER BY with OFFSET/LIMIT more efficiently than sorting all the records? The algorithm for a simple TOP x is easy enough. It takes one full pass ...
1
vote
0answers
644 views

What database structure is suitable for tracking File audits?

I need to track permissions and access requests to a file server in a database. I'm given the full path of the folder and am considering parsing the path (splitting on the "/" character) and creating ...
1
vote
1answer
183 views

Using Entity Framework with third party applications

In my current situation I am one of two developers at a manufacturer. We have been extending the functionality of existing applications (ERP and some others). These third party applications do not ...
1
vote
1answer
91 views

Custom Domain or Attribute Reference Table or Both?

I'm designing my first real database and I'm weighing the pros and cons of how to enforce uniformity in my data. My first option is to create a custom domain and program my UI to only allow certain ...
0
votes
0answers
28 views

Test strategy for liquibase changes

I am part of a team that works on a SpringBoot application and we use liquibase for maintaining our database changes. Config data that has to be added/removed/modified goes as part of a change-set. ...
0
votes
0answers
22 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
0answers
29 views

spring boot jpa repositories vs hql queries vs native queries

I'm currently working on a Spring Boot project. I have a big dilemma between the usage of JPA repositories, HQL queries, and SQL native queries. Converting all query logic to a native query is a ...
0
votes
0answers
44 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 ...
0
votes
1answer
60 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 ...
0
votes
0answers
59 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 "...
0
votes
0answers
56 views

The best approaches to implement query with pagination based on SQL DB (Oracle)

I have faced with the following situation: there is a grid with pagination and a filter criteria builder to filter grid data set in web UI, in backend there is a Java method, which analyze filter ...
0
votes
0answers
152 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) { ...
0
votes
0answers
523 views

How to store articles (with text and images) in SQLAlchemy database

I'm working on a blog-like application with Flask and SQLAlchemy and I'm unsure how to store the blog posts (articles) in the database. These are going to contain text and images (placed between ...
0
votes
0answers
23 views

Manipulating a large dataset before inserting in Azure Search

I have an Azure SQL database with 20 million + records that i need to manipulate (including joining other tables, etc) before inserting into Azure Search. The procedure needs to be reproducible in ...
0
votes
0answers
167 views

Tradeoffs between Testability and Performance when filtering using SQL vs LINQ

My team has recently decided to adopt the CQRS pattern similar to the one used in the famous Microsoft EShopsOnContainers repository. In this solution, the queries are made in Query classes using ...
0
votes
0answers
41 views

Design a way for multiple and single group mapped to a Role?

Currently I am trying to setup a relationship between user groups and roles. I am trying to satisfy: Multiple roles apply to single group multiple groups can have same role multiple ...
0
votes
0answers
40 views

Ranking / Generating score card based on scoresheet details

I am busy with a score keeping app. And would appreciate suggestions/ help. I will try to be brief and hopefully have explained the main gist of the app below The app basically maintains some scores. ...
0
votes
0answers
64 views

Is data binding in an SQL RDBMS a fruitful endeavour?

I've found that a common task for web applications I've built is to react to a change in the state of the database. That is, any user interaction that affects the persistent state may change views ...
0
votes
0answers
647 views

Javascript and SQL Lite (multi browser offline SQL/database query)

I'm in the elections division of my county and am trying to simplify a voter lookup method for our poll judges during election time. Currently we are using a clunky heavy application that the judges ...
0
votes
1answer
30 views

Retry mechanism - 3rd party provider

I have the scenario below and what I want to do is to build a retry mechanism to retrieve a status. So, what we have is a payment system and we want to call a 3rd party provided periodically in order ...
0
votes
1answer
651 views

Address search from large text file

Basically I want to develop a Address lookup(part of my project) using C# (and I can use SQL if necessary). I have a very large text file which have all the UK address and postcodes. Addresses needs ...
-1
votes
1answer
165 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 ...
-1
votes
1answer
49 views

Best practices for creating structured data from "flat" SQL query results

Recently I've been working on a project to teach myself PHP and SQL, and as the project has gotten more complex I've been wondering what the idiomatic approaches are for creating the backend models ...
-1
votes
1answer
36 views

Cursor select on 2nd Primary key on db2 sql field for embedded positional value - Unable to determine most efficient for longterm design

I have a SQL DB2 table where the first two fields are the primary keys (not including the third field which is date/time stamp). The table was designed by another team with the intent to make it ...
-1
votes
2answers
165 views

Relational or non relational database for my described application?

I want to design and implement a mobile application in which: Every user who have already registered is able to add new news (including title: text plus a picture, and content: long text, pictures or ...
-3
votes
1answer
123 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 ...
-3
votes
2answers
650 views

Database Schema for a multiplayer quiz game

I'm trying to accomplish this scenario : There are 2 types of users, let's say Admin and Worker, and there have different roles. Admin can do a CRUD of questions, and also can create a room where ...