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.

2
votes
1answer
64 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
103 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
67 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
76 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
44 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
58 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 ...
42
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
75 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
169 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
28 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
146 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
54 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
62 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
118 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
495 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
360 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
300 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
50 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
247 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
44 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
84 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
317 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
1answer
82 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
340 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
713 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
74 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
141 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
155 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
92 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
535 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
176 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
157 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 ...
117
votes
14answers
33k 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: ...
0
votes
0answers
28 views

JSON in relational DB column for 'custom' data - how to present it on web page

I am creating a generic telemetry system, which records some data about client app usages. Technology used: ASP .NET MVC with EF (code-first) hosted on Azure. There is a requirement to be able to ...
0
votes
1answer
18 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 ...
1
vote
1answer
223 views

I need Auto-incremental like key but how?

I have a business specific pattern for storing their IDs. It's in the format of yy-mm-autoincrement. I could just store date and incremental id but there's another problem. It should be restarted ...
0
votes
1answer
33 views

Extending photo album app with videos

I have a personal photo album application where I upload my photos. However, I'd now like to extend it with videos as well, and I'm unsure how to properly perform this extension. The current design ...
0
votes
1answer
99 views

Event sourcing and criteria based searching

I have done a lot of reading about Event Sourcing in my spare time recently. I understand that it is for situations (Bounded Contexts?) where the business is interested in how an entity arrived in ...
4
votes
2answers
5k views

Why should I use foreign keys in database? [closed]

In my 10+ years of experience in the IT field, I have never used foreign keys in any of my project and I never felt the need. I did work with professional databases which had foreign keys constraints. ...