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
2
votes
1answer
71 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 ...
0
votes
0answers
29 views

How to keep track of db tables used in various apps

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
141 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
100 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
117 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
44 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
72 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
10 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
108 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
70 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
80 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
53 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
73 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
82 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
171 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
31 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
152 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
59 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
65 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
6answers
1k 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
72 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
443 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
342 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
51 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
336 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
62 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
37 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 ...
0
votes
1answer
87 views

Looking for design ideas to handle about 10 sql queries with 10 separate output files

I have a list of queries to use for an array of associated output files. I'm having a block as to what a good design would be to put it all together for my queries to be executed and the output sent ...
-2
votes
1answer
21 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
353 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
154 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
439 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
876 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
79 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 ...
92
votes
9answers
16k 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
179 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
180 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
107 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 ...
2
votes
5answers
536 views

Is it safe to put SQL statements in my C# application?

I'm making a medium sized application to be distributed privately to several Churches. It connects to an Azure database but it does not have any server managing the information (apart from the SQL ...
1
vote
1answer
89 views

Best Practices for constantly checking a table

I need to check for the existence of a record in an SQL database. If it exists, create a printer stream and send it to a label printer. My issue is that I have as many as 20 of these services ...
3
votes
2answers
179 views

Is using -1 as a special Id a good idea in SQL?

I'm trying to determine if using '-1' in an Identity column has any drawbacks, or if there is a better way to do what I want. In my database I have User table and a Group table. All users must belong ...
0
votes
1answer
47 views

Fastest way to return a list of records depending on many many-to-many relationships

I'm developing an API in Rails and the following requirement is established (it's really more complicated, but I want put a simple example): There are users and messages. users have a gender (...
2
votes
2answers
205 views

Is it better to have a single query that returns all related entities, or separate queries for each case?

Summary When writing methods to query for related entities, is it better to have a single method for all related entities or separate methods for each combination? Full Problem I'm writing ...
118
votes
14answers
34k views

Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?

Is there any engineering reason why is it like that? I was wondering in the case of a RDBMS that it had something to do with performance, since a "YEAR" is more specific than a "MONTH", for instance: ...