Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
44 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
50 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
116 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
198 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
70 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 (...
1
vote
2answers
206 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
237 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
47 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
139 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
61 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
36 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
40 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
64 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
20 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. ...
0
votes
3answers
172 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
41 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
75 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
437 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
64 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 ...
88
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
91 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....
3
votes
1answer
101 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
71 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
527 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
86 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
175 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
150 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 ...
116
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
24 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
17 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
153 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
93 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 ...
2
votes
2answers
2k 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. ...
3
votes
3answers
1k views

DB Queries in code or use Stored Procedures

Some of my colleagues tells me that using stored procedures in the database adds to much business logic in the database, and that you should keep the data separate from logic. Other colleagues tells ...
5
votes
2answers
167 views

Is it worth implementing Entity Framework when calling a single stored select?

I'm currently building a web application for my enterprise level business internship. This web application will track data from three different sources: a DB2 database, a SQL Dynamic database, and an ...
3
votes
1answer
113 views

Alternatives to SQL for describing data mappings

Back story: I work for a startup which was acquired by a large Fortune 500 company. I'm the lead engineer on a project which aims to consolidate duplicate data stored by both companies. Our new ...
-1
votes
2answers
164 views

SQL Coding Naming convention: Variable same name as Columns

Is there a coding naming convention principal for SQL variables? Can variables be named the same as column names, I heard in different companies, this may cause confusion. However in C# below, its a ...
2
votes
3answers
233 views

Database design for generic behaviour

I have a few tables that are generic. One of them allows a user to comment on something, and the other one allows a user to favorite something. Initially, I had thought that to make it possible to ...
0
votes
0answers
70 views

Handling Failed DB Insert

I have a application that constantly checks for messages inside the Rabbit MQ. When a message is received, I will parse that message and insert it in the DB. I am wondering. What if it fails? What ...
1
vote
1answer
111 views

Keep enumeration in DB when data associated with it is not storable in DB

I've seen several questions on this website explaining that enums should be kept in the database when there is one (this one for example). However, I feel like my case is different and there are extra ...
2
votes
4answers
455 views

Strategy to improve performance on key-value pairs table

The situation Our system has a MySQL database with a product table whose parameters can't be modified. In order to add optional extra parameters, a developer designed a key-value pair table which ...
-1
votes
1answer
23 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 ...
0
votes
1answer
44 views

Designing an efficient search by tag solution

I've a project where the users will be able to tags themselves with skills tags (maybe predefined in the system) , and then the project managers on a company would be able to search the best user with ...
0
votes
1answer
83 views

Database design: where to store a “current” or “selected” flag in a parent-child hierarchy [duplicate]

Sorry, maybe it's a well known design principle, but because I don't know a name for this kind of relation, I was not able to find any existing answer. So, let's say we have a parent which may have ...
0
votes
2answers
72 views

Best practice for object relation mapping to execute delete/update

In doctrine (or any ORM in general), the easiest way to execute a delete/update operation is to select such entity, then remove entity or update single attribute in the code, ended by persist the ...
0
votes
3answers
200 views

Database Auditing: “Updated At” Null or Not Null?

Currently, we've been working on the architecture team on defining the database models. What has been troubling me is my superior's advice when it comes to working with audit fields. He advocates for ...
1
vote
0answers
44 views

PHP MVC - Single action for multiple similar views or one action for each?

For example, if I have 3 database tables that have similar columns/attributes: cats, dogs, bunnies Each table has a view that consists of an html table listing all records in the table, and a form ...
-1
votes
2answers
514 views

Nullable foreign keys and creating join tables

I'm not that experienced in sql modeling and i'm looking for input on this because it's important for me to get it right. I use mssql and entityframework core in an asp.net core web app. The app ...