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
-4
votes
0answers
18 views

How to cleanly write SQL get functions in PHP with params?

I'm working on a PHP application where we have helper functions to run our SQL queries on our MariaDB database. The queries are formatted as strings and then we call a helper function select, update, ...
-1
votes
2answers
96 views

Is it true that ORM allows for changing the type of SQL that you use easily?

Hey so I have been looking into ORM since my last question and I am wondering. Instead of worrying alot right now about whether I will use mysql, postgre, or sqlite, would it be better to use ORM ...
-3
votes
0answers
53 views

How can I create a database in C++? [closed]

I've been given a homework at university that basically asks to create something similar to SQL using only C++ language. (I've never used SQL so I don't even know how it works) It means that if the ...
-2
votes
4answers
127 views

Are there any guidelines about making SQL queries easier to comprehend?

As I'm spending more time working with SQL, I'm starting to feel like this language promotes logic obfuscation. Here are some examples: An INNER JOIN in a query can act as an implicit filter that ...
-2
votes
1answer
41 views

Design a database with multiple relations and tables

I'm struggling to find how to correctly design a database in order to store and retrieve data for my software. Basically, I'm designing an application for a gym (C++, MySQL with PhpMyAdmin): each ...
0
votes
0answers
17 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 ...
4
votes
4answers
1k views

Is this WHERE clause builder an over-engineered design?

I've got to build some somewhat complicated WHERE clauses in SQL for a project I'm working on, and the clauses feel very hierarchical with their combination of ANDs and ORs. Instead of: WHERE ([...
0
votes
0answers
16 views

Live Streaming Service Schema Design

I'm requesting some feedback on a database we use in a live streaming service. We've Frankensteined this one as we've grown, and I'm looking to make a new one because there are now some obvious pain ...
1
vote
1answer
41 views

SQL Database schema for Catering/Menu management

Background I'm building a Private Chef booking service where you can book a Chef to cook you a custom Menu. I'm having trouble creating a SQL db schema that accurately represents the domain while ...
-2
votes
1answer
31 views

Saving large files like Credit Reports in Data Store - SQL/NoSQL/ObjectStore

I am working on a project to save external credit bureau reports in database. These reports are typically big and could go from 0.5MB to 5.0 MB. Number of files will grow exponentially over the time ...
-4
votes
1answer
57 views

Is it better (from a maintenance perspective) to dynamically construct complex SQL queries using Jinja or String concatenation? [closed]

Two possibilities. Which is better, with respect to readability, maintenance and clean code: SQL injection can be disregarded. Constructing complex parameterized sql queries using python string ...
1
vote
1answer
51 views

Doing data manipulation on server side vs client side

Im building a project that stores time series data on a per user basis. On the dashboard of the user it'll show some simple statistical analysis like averages but more importantly, it'll create charts ...
0
votes
0answers
30 views

How to model combinatorial information in RDBMS

I need to model the persistence of combinatorial information. For example, suppose that the combination of 3 attributes (A, B, and C) are used to reference a given product. Besides that, supposed that ...
-2
votes
1answer
52 views

Database design user user roles applications and features

We have multiple applications with different features. We want to create a central security system, where each user is assigned a role. Role has access to certain applications and features with in ...
-1
votes
1answer
42 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 ...
3
votes
1answer
146 views

What *really* is the difference between relational and non-relational data?

Here is where I am at right now: I know that relational data, like that found in relational databases like MySQL or Postgres, is relational because there are relations between the tables. That is the ...
1
vote
0answers
24 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 ...
2
votes
0answers
20 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; }...
2
votes
3answers
204 views

How can I scale a database that requires that writes are consistent?

Suppose I have a table that saves plane ticket purchases. I need to keep track that I don't oversell so when a user purchases a ticket it needs to be stored in that table and other customers need to ...
0
votes
0answers
69 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 ...
1
vote
2answers
91 views

Database design of a web-app which trains kids to read

The question is about my database design. Is it OK? I feel uncomfortable about having 2 separate WordTraining and SyllableTraining tables and about too simple design (not much normalized). Maybe ...
0
votes
1answer
54 views

Analyse/show user why data has been filtered out by SQL

Sorry for the bad title. Couldn't really think of a good name without an explanation. In our system (source is inside Oracle PL/SQL packages) we have quite a lot large SQL queries with a sh..load of ...
0
votes
3answers
88 views

Keeping up logical order in a messaging environment

My setting is the following (Please assume that this points are entirely rock solid unchangeable, some for good reasons some: "just because"): In a super scalable microservice environment i receive ...
1
vote
5answers
199 views

How do B-Trees used in databases sort data based on different fields?

I know majority of databases uses B-Trees, and I can see how using a balanced binary tree will give fast sort times, for ordering by ID or whatever else the primary key is; but how are databases able ...
2
votes
2answers
200 views

Should POST and PUT handlers do pre flight check for 404?

When implementing GET on a resource is makes sense to respond with 404 if the resource cannot be found. For POST and PUT verbs it is a little more complicated. To respond with 404 in that case I ...
5
votes
1answer
116 views

Are legacy C# DataTable/SqlDataAdapters exponentially faster than SqlConnection/SqlCommand and/or LINQ to SQL DataContext.ExecuteCommand calls? [closed]

I can't believe DataTable/SqlDataAdapter massively beat out System.Data.Linq.DataContext.ExecuteCommand and ExecuteNonQuery (tried with both Stored Procedures and command text) and just straight ...
0
votes
1answer
89 views

Splitting 1-1 relationships across lots of different tables?

I've come across a table that has about 200 columns. About 150 of these can be grouped into 5-10 tables that make real world "sense", and seeing as most of these entries are never used I figured it ...
0
votes
1answer
52 views

MVC database connection .NET

Introduction I intend to create a .NET WinForms application (this will be a toy application) which connects to a SQL Server database backend. I have designed and implemented an object-oriented model ...
-2
votes
3answers
198 views

Best Practices for Managing SQL Code

I have a issue that keeps coming up as a developer using any .NET (C# mostly) and SQL. I personally feel it is bad practice to build SQL statements in code. There are too many case scenarios that may ...
1
vote
1answer
41 views

Query by NoSQL keys versus SQL hash

I am trying to decide between a NoSQL (MongoDB) or a SQL (Oracle) database for my project. This database will be used to store output files from a set of parameters. I can model this as a MongoDB ...
1
vote
2answers
185 views

What's more expensive on my server-side processing?

So I want to be processing my security protocols server-side, mainly token checks and then role authorisations for certain actions. I'm using c# .net core connecting to an MS SQL database and ...
6
votes
4answers
601 views

Relational integrity without relations

I have about 15 years software engineering experience, writing business software with relational databases. Mainly SQL Server and Oracle. I've always been of the opinion that you should define ...
3
votes
2answers
323 views

Database design - maintaining versions of an object

Suppose I've a Person object. This Person object in turn is having name, address, accountNumber and many other fields. Now my requirement is that I want to maintain multiple version for each person ...
1
vote
2answers
127 views

Bidirectional sync between two databases with differing schemas

I'm embarking upon a project where the goal is to create a bidirectional sync mechanism between two database systems with differing schemas. One is a custom CRM app build on MongoDB, the other is ...
1
vote
3answers
59 views

Having unique identifier for user while Updating user entity (by deleting and adding new one)

I am adding authentication to my web app and I consider the following case: I have USERS table which look like this simplified version Id {int} | Username {nvarchar(100)} | CollectedPoints {int} | ...
1
vote
0answers
31 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
2answers
118 views

SQL Finding duplicates across databases across machines

I have multiple databases (MySQL, all identical except data, can delete arbitrarily from any) across multiple machines which contain large quantities of sentences (~ 1e+11 per database, ~ 4e+12 in the ...
1
vote
3answers
110 views

How to make a data warehouse of a single full Database?

After learning about the data warehouse concepts whole day, I couldn't found a source that can actually say when you should not do data warehousing. In most tutorials I read, it says that DW is for ...
4
votes
4answers
420 views

Object Oriented Design and Relational Databases: Where to place behavior that pertains to more than one tables?

I have been trying to learn Object Oriented Design, but I find it very difficult to model objects that access a relational database and do JOINs. For example, I am building a REST API, it has User, ...
1
vote
3answers
120 views

How do I query something when I don't know how its structured?

How does one write & run queries against a database/warehouse if they have no knowledge of how the data is structured? By "structure" I mean table names, fields, etc. Unless I have table names, ...
11
votes
4answers
627 views

How to review sql changes more effectively? [duplicate]

From my experience, sql code changes almost always tend to be NOT incremental: someone creates a new stored procedure, or modifies an entire embedded sql query for optimization purposes, or creates a ...
0
votes
0answers
19 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 ...
3
votes
1answer
148 views

How to Design for Monthly Database Refreshes?

I get a database every month with the prior month's data in it. There are three types of data: flag settings, demographic and transactional. I need to import the data into my standing database. ...
0
votes
2answers
132 views

Is there an industry precedent for SQL pass-through endpoints? [closed]

There is an increasing interest toward GraphQL and Falcor across the Web. Each time I see an article or discussion about those, I get reminded about SQL, at least its DQL part. Obviously, sending raw ...
2
votes
1answer
92 views

Designing Models from complex ERP SQL tables

I have a project coming up where I'm supposed to upgrade an existing access app to a .net architecture. I'm wondering how to design object-oriented models from the giant tables and multiple ...
1
vote
2answers
109 views

How to keep track of db tables used in various apps [closed]

I have N applications using ORM, SQL-statements and stored procedures to access M tables from an MSSQL Server 2017. There are some shared tables that are used by various apps. Let's say I am forced to ...
-1
votes
3answers
145 views

General SQL Question [duplicate]

Let's say a user goes to the log in page of my website and creates an account. Instead of entering their real name, they decide to write some sql code and try to gain access to my database. I have ...
0
votes
2answers
120 views

Ensure unicity of a 10 characters alphanumeric String

I have a requirement of developing a service that must generate each day few millions of random and unique alphanumeric String of length 10 (I can't increase this length, this is a customer ...
0
votes
1answer
140 views

Database Design Structure

I am building a WebApp which has a list of Assets. I have a peculiar requirement and I am unsure of the best way to store the data in SQL. Requirement: My Asset Headers currently have a relation to ...
0
votes
1answer
181 views

Bridge/Join Table for a Many to Many Relationship between One Table and Multiple Others

Background I have a relational database an I want to have a table that contains flags. I want multiple tables in my database to have a many to many relationship with the flag table. Options I ...

1
2 3 4 5
14