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
-3
votes
0answers
55 views

Is it possible to use Excel as an editor for SQL instead of SSMS for example?

I wondering for one project if something like this is possible. I know that I can pass data from SQL to Excel easily, but is there a way how to pass data from Excel to SQL instead of saving .xls and ...
4
votes
4answers
363 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
115 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
565 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
17 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
106 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
126 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
79 views

Designing Models from complex ERP SQL tables

I have a project coming up where im supposed to upgrade an existing access app to a .net architecture. Im wondering how to design object oriented models from the giant tables and mutliple ...
1
vote
2answers
100 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
143 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
106 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
123 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
50 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 ...
2
votes
1answer
76 views

Can I reduce locking for this scenario using Hibernate and H2

My Java application uses Hibernate and H2. It process music files within folders. Typically for a folder it would read the files in the folder and store each file as a Song class in database, then ...
0
votes
0answers
11 views

working with unordered subsets of data: temporary vs. permanent table

I'm writing rather simple database application where user should be able either to work with the whole dataset, or to manually select certain records to work with and leave in the current grid view ...
0
votes
0answers
147 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 ...
2
votes
1answer
73 views

Allow client side direct access to the database [duplicate]

I am asking advice from the SQL graybeards. Because my following proposition goes against everything I have practiced. In this instance Let's assume we are developing a phone app. A social network ...
0
votes
0answers
32 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
18 views

Modelling Azure Consumption API Tags in SQL server

After getting some data from the azure consumption api, there is a field called 'tags' which contains some predefined tags and their values. I am trying to come up with a way to model these in a SQL ...
2
votes
3answers
89 views

What is a good datastructure for localizable tags? (Like a blog post or questions at SE getting tagged with)

I am developing a web application where users can upload images and add tags to them. Because the audience is very international, I want the app as well as the content to be localizable. Most of the ...
39
votes
6answers
5k views

Why Isn’t SQL More Refactorable? [closed]

Everyone knows that new developers write long functions. As you progress, you get better at breaking your code into smaller pieces and experience teaches you the value of doing so. Enter SQL. Yes, ...
0
votes
0answers
67 views

Designing database relationships for a voting application

So I designed database relationships for a voting/polling application. I want a user to only be able to pick 1 option on a poll. I want multiple options per poll. So what I have here is this. User ...
1
vote
2answers
90 views

Branching SQL relationship to C# object model

I'm developing a C# WPF desktop application where I need to read/write to an SQL database regularly. Now I want to map the data from the database to objects in C#. I can't use Entity Framework so I'm ...
43
votes
8answers
8k views

Is domain driven design an anti-SQL pattern?

I am diving in the domain driven design (DDD) and while I go more deeply in it there are some things that I don't get. As I understand it, a main point is to split the Domain Logic (Business Logic) ...
-2
votes
1answer
105 views

Generate HTTP RESTful server from database schema

Today the job of most Java-based servers is to translate HTTP requests into SQL commands/queries and send a JSON-ed response back to the client. That's a pretty generic (and honestly boring) job. I ...
0
votes
1answer
172 views

An approach to writing database applications [closed]

I'd like to share my approach to writing database applications for small businesses. It's possibly unconventional, but it's something I have been developing for nearly 20 years. The approach I use is ...
0
votes
0answers
36 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
3answers
156 views

Keeping objects in RAM vs more queries to the database

So this is my DataStructure: Project - Name - ID - Image - History - User - Comment When my application first starts it is pulling all projects with all details. obviously ...
1
vote
1answer
63 views

Avoidance of Lost Update in DB2 for z/OS

I have a question concerning the "Uncommitted Read" Isolation Level under DB2 DBMS for z/OS. In this article https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/perf/src/tpc/...
0
votes
2answers
68 views

Most efficient way to get unique customer count

I'm working on a system that stores details of customer purchases for several stores. One statistic that they would like is to know how many unique customers they have had over a specified day range, ...
2
votes
4answers
119 views

Date comparison with the last second of a day

Would you consider WHERE SomeDate <= '2018-01-01 23:59:59' instead of WHERE SomeDate < '2018-01-02' When the intention is that SomeDate goes no further then 2018-01-01 a code smell and if ...
3
votes
7answers
2k views

Should I use entity framework for CRUD and let the database handle the complexity that comes with high end queries?

I am new to ef and liking it since it reduces the overhead of writing common queries by replacing it with simple add, remove functions. Agreed. Today I got into the argument with my colleague who has ...
1
vote
1answer
75 views

Database Authentication - Are 2 database trips better than 1?

Scenario I have an application where some operations require the authentication of an admin. Example Steps: I need to validate the admin's username & password insert a row into a MySQL table (...
0
votes
3answers
529 views

Should I use ISO country code for primary key, or integer ID?

I have or am going to have a database with countries and their emissions. Countries in the API have a 3-letter ISO Code. Should I use this for identifying the countries in database, or just a plain ...
-3
votes
2answers
351 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 ...
0
votes
1answer
52 views

Using sql triggers over higher level scripts

I recently came across a set of possibilities for creating rows in a table in my database. the scenario is that I am trying to populate a notifications table by different types of notifications data ...
0
votes
3answers
370 views

How can I interpret a SQL Check Constraint inside my C# .NET class libary?

I was given a more or less complex task. The goal is to interpret a SQL Check Constraint inside my C# .NET libary. In our case we have a simple UI that displays what is inside the database. We do not ...
0
votes
1answer
63 views

Best way to encode a queryable number to another unique queryable number? (database outsourcing)

So lets say you want to outsource your database to a third party and be able to query on it on their server but you want to "encode" the salary column of the users on their server, aka hide the real ...
-1
votes
1answer
38 views

TEXT column as key for a table when there are other tables referencing that table?

I have a UNIQUE TEXT column (“name”) in SQLite for one of my tables (let’s call it “table A”) and i’m considering using that column as the key for the table The reason i can see for continue using a ...
0
votes
1answer
47 views

Horizontally Scalable Streaming of SQL Query Results

I have a service that runs queries on a database and gets (potentially) large number of rows. I also have services that can export a given set of rows to csv, xlsx or pdf etc. I have separate export ...
-2
votes
1answer
22 views

Filtering the Data and summing on one field Should I do in JAVA or write query with aggregate query with filter clause?

I have one table in which there would be millions of records. The requirement is to the records based on the condition and then total on one of the fields. Selectivity would be around approx 50. ...
1
vote
3answers
367 views

Business logic contained inside JSON

I have recently looked at some Web APIs provided by well known companies' e.g. this one by HSBC: https://developer.hsbc.com/swagger-index.html#!/Products_Commercial_Credit_Cards/...
1
vote
2answers
207 views

Using UNIQUE (col1, col2) or using a composite primary key for a relation table in SQLite/SQL?

i’m working on a project where i’m using SQLite and where i have a few relation tables looking similar to this one: CREATE TABLE tag_entry_relation ( id INTEGER PRIMARY KEY, tag_id INTEGER REFERENCES ...
1
vote
1answer
538 views

Designing SQL table for prices changing in time

I'm developing a database with a price list table for my workplace. I came with two major options, and would like to have your opinion :) It's rather simple, containing: Id, description and price ...
2
votes
3answers
1k views

Database : Does it make sense to choose the zipcode as a primary key for an address?

I was following a tutorial on the normal forms of SQL databases, and I got confused landing on this example : https://www.tutorialspoint.com/sql/third-normal-form.htm. From CREATE TABLE CUSTOMERS( ...
2
votes
1answer
84 views

Proper way to associate data to users that are stored in external database?

I have this IdentityServer which handles authentication and authorization. It saves users, roles, claims, etc in its own database. Then, I have a web app that needs to protect some resources to ...
94
votes
9answers
17k views

Is it ever okay to use lists in a relational database?

I've been trying to design a database to go with a project concept and ran into what seems like a hotly debated issue. I've read a few articles and some Stack Overflow answers that state it's ...
1
vote
1answer
245 views

Managing heterogeneous data

The underlying problem is one I seem to run into a lot. You have a collection of objects with some things in common, but other information about the objects may apply to some of them but not to others....
2
votes
1answer
196 views

How to think about queries in NoSQL

I'm writing a project for school - Tinder-like app. I thought of implementing database entirely in RDBMS (PostgreSQL). But now I think it's a good opportunity to get to know NoSQL systems a bit. I'm ...
1
vote
1answer
109 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 ...