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.

2
votes
3answers
232 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
83 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 ...
1
vote
0answers
36 views

Difference between Hints and Indexes [migrated]

Oracle Hints Hints can extend the capability of cost-based optimization. This technique is especially helpful if your distributed query contains an aggregate, subquery, or complex SQL. Indexes ...
3
votes
2answers
162 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
140 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 ...
114
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
23 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
16 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
121 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
29 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
68 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 ...
1
vote
2answers
589 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
270 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
149 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
111 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
150 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
190 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
66 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
93 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 ...
1
vote
4answers
180 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
21 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
41 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
57 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
132 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
40 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
265 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 ...
1
vote
1answer
61 views

Table design with Microsoft SQL Server

I was hoping to get some advice from you folks on my table design methodologies (specifically in MS SQL if that matters). Let me start by showing a couple of example tables that I might have in a ...
-2
votes
1answer
75 views

Correct terminology for a table which provides “dynamic columns”

A common pattern in database design is to append dynamic columns to a table with a design like so: create table MasterTable ( Id int not null primary key , Name nvarchar(64) not null unique ...
1
vote
2answers
94 views

Using Autoincrement Primary keys as Foreign Keys vs. unique codes

i’d like to push discussion on the same named thread again. I also use auto increment primary keys as foreign keys - mostly for code tables. Every row also has a code for identifying entries for ...
1
vote
3answers
221 views

Why can't clients query the DB directly using SQL?

Given that a lot of modern web applications just consume JSON data from the sever, what is the point in having a REST api at all instead of just using SQL's query language? Nowadays people are ...
2
votes
1answer
71 views

Does using MEAN stack reduce the need for code and data structures compared to ASP.net with SQL?

I've been writing web applications for almost 10 years. The ASP.net stack has been my bread and butter and although it has a lot of great things about it, I honestly never chose ASP.net. It's just a ...
-4
votes
1answer
87 views

Running SQL queries and getting back the result on deployed software

We update the software manually every couple of weeks (by copying over files).For the database we get their backup, apply the changes and hand it back over.In the meantime when we do a bug fix it's ...
1
vote
0answers
129 views

How to Investigate the difference between query execution times?

I have a simple query, when executed directly in SQL Developer Studio (It's an Oracle Database) takes around 4 seconds. But when executed via my Java code it takes around 20 seconds. I'm using ...
3
votes
2answers
263 views

Is using spacing effectively equivalent to the long method code smell?

There's a common code smell involving long methods with the most common answer being that methods should be really small, less than 50 lines per say (or 20). I understand why this is because it ...
0
votes
2answers
339 views

SQL table with many columns or more 1:1 tables?

What is better solution? Eg. an object has a lot of single settings. Keep all these settings in one table or divide these features and store in separate tables with relation 1:1? Some features are ...
0
votes
2answers
242 views

Migrating an Application from SQL to NoSQL [closed]

I am faced with taking a mvce's for SQL which happens to be the WideWorldImporters Database from Microsoft and migrating it to a NoSQL on mongodb. The system in question may use certain fractions of ...
1
vote
1answer
134 views

Can I get information about an SQL SELECT query (such as the tables it accesses) using C#?

The c# application I'm working with executes many T-SQL SELECT queries that are stored as strings in a database. These queries almost all involve selecting data from and joining with several tables ...
3
votes
2answers
674 views

SQL algorithm for most overlapping events

The Problem I'm looking for a query to help me solve the following: I have a series of events Each event has a start and end date. Many of these events overlap The answer I'm looking for is the ...
-2
votes
2answers
116 views

Are there benefits to having a SQL-like read only database layer over a Blockchain distributed ledger? [closed]

Distributed Blockchain datastores provide inmutability to recorded transactions and only allow to add records accepted by a consensus algorithm in sequence. You could store data on the blockchain or ...
0
votes
0answers
29 views

Create search functionality for a php based chat system

I want to create a search functionailty for a forum's PM system which has to fetch from a DB containing usernames and messages. The forum is based on php (phpbb). It should be moderately smart. ( able ...
0
votes
3answers
358 views

Can traditional SQL databases be fully replaced by NewSQL?

I've recently learnt about "NewSQL" databases like: Google Cloud Spanner (proprietary) Microsoft Azure Cosmos DB (proprietary) CockroachDB (open source) also see How does CockroachDB compare to ...
-1
votes
1answer
36 views

Measure ActiveRecord performance in `limit` method [closed]

How can I measure ActiveRecord's performance? My query has where and an order by clause, and on top of that I want the top row: User.where(…).order(id: :asc).limit(1) Knowing that the order by ...
-2
votes
4answers
121 views

One Single Table or Two Smaller Tables

We currently have a Users table in the database, this has quite a few columns in it. Around 50% of the columns are barely used in the system (only on one or two pages). We've been discussing amongst ...
0
votes
1answer
73 views

Developing SQL <-> REST Adapter

This is a very conceptual question and looking for advice or examples you may know about. I'm just getting back into development after a very long hiatus in a sales career, so please excuse me if my ...
1
vote
2answers
70 views

Sql: Pre inserting relational entities vs the “lazy creation” of the entities

I have the following entities in my database (it's simplified): Student (id) StudentCourse (id, student_id, course_id) Course (id) Lesson (id) LessonCourse (id, lesson_id, course_id) ...
1
vote
1answer
55 views

architecture of gui app with several indepedent windows and sqlite backend

Let's assume that I have such a simple database scheme: CREATE TABLE tbl(key INTEGER PRIMARY KEY, cap TEXT NOT NULL); I want to show at least three independent GUI windows. One window (window A) ...
1
vote
1answer
69 views

Database Schema design of server running status log

I am working with 4 AWS EC2 instances (servers). Each Instance has Instance ID and Instance Name. Instance ID is unique. Each of them having multiple application servers, like on of them is running ...
1
vote
1answer
354 views

Should I use NoSQL or RDBMS for materialized view in this CQRS / ES implementation?

I am going to try to build a implementation of CQRS and ES for large scale application (1M users) authentication API. Below is my initial architecture draft (Ignore Azure related stuff). I am going to ...