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.

44 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5 votes
2 answers
474 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 ...
Ian's user avatar
  • 159
4 votes
0 answers
677 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 ...
yellowduck's user avatar
3 votes
0 answers
89 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> ...
Alexey's user avatar
  • 237
3 votes
0 answers
300 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, ...
Myonara's user avatar
  • 131
3 votes
0 answers
523 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 ...
José's user avatar
  • 131
3 votes
0 answers
376 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 ...
user12345's user avatar
  • 131
2 votes
0 answers
38 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 ...
SilentStorm's user avatar
2 votes
0 answers
71 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] ...
Scott's user avatar
  • 21
1 vote
0 answers
115 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" ...
user2563087's user avatar
1 vote
0 answers
39 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 ...
Pierre-Alexandre's user avatar
1 vote
0 answers
73 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 ...
Eddie Hatfield's user avatar
1 vote
0 answers
199 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 ...
shiafisher's user avatar
1 vote
0 answers
205 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 ...
Anonymous Creator's user avatar
1 vote
0 answers
53 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 ...
Saryu Labs's user avatar
1 vote
0 answers
953 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 ...
Paulo Schreiner's user avatar
1 vote
0 answers
132 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 ...
John Black's user avatar
1 vote
0 answers
641 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 ...
Can't Tell's user avatar
  • 1,161
1 vote
0 answers
168 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 (...
user1673665's user avatar
1 vote
0 answers
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: +-...
George T's user avatar
  • 295
1 vote
0 answers
297 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 ...
david.pfx's user avatar
  • 8,115
1 vote
0 answers
648 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 ...
makerofthings7's user avatar
1 vote
1 answer
269 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 ...
Matt's user avatar
  • 111
1 vote
1 answer
101 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 ...
Jonathon Anderson's user avatar
0 votes
1 answer
157 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 { ...
Francis Ducharme's user avatar
0 votes
0 answers
130 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 ...
aterbo's user avatar
  • 111
0 votes
0 answers
72 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 ...
JrCaspian's user avatar
  • 125
0 votes
0 answers
25 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 ...
GradientJagger's user avatar
0 votes
0 answers
211 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 ...
Yashwanth somayajula's user avatar
0 votes
0 answers
286 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) { ...
rytisk's user avatar
  • 109
0 votes
0 answers
41 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 ...
Rhodes73's user avatar
0 votes
0 answers
184 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 ...
Josh L's user avatar
  • 103
0 votes
0 answers
46 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 ...
shaselai's user avatar
0 votes
0 answers
64 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. ...
alphacoder's user avatar
0 votes
0 answers
76 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 ...
concat's user avatar
  • 517
0 votes
0 answers
663 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 ...
Eric's user avatar
  • 1
0 votes
1 answer
43 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 ...
pik4's user avatar
  • 375
0 votes
1 answer
725 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 ...
user73809's user avatar
-1 votes
1 answer
179 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 ...
Panzercrisis's user avatar
  • 3,153
-1 votes
1 answer
123 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 ...
Jumhyn's user avatar
  • 162
-1 votes
1 answer
41 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 ...
Cindy LB's user avatar
-1 votes
2 answers
167 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 ...
Mart's user avatar
  • 1
-2 votes
1 answer
53 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 ...
geofflittle's user avatar
-3 votes
1 answer
485 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 ...
Mats Gausdal's user avatar
-3 votes
2 answers
887 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 ...
Skizo-ozᴉʞS ツ's user avatar