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
5answers
539 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
183 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
210 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: ...
0
votes
1answer
21 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
275 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
35 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
107 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 ...
8
votes
2answers
8k 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
4k 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
243 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
115 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
199 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
347 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 ...
1
vote
1answer
156 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
2k 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
26 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
63 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
126 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
78 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 ...
1
vote
3answers
405 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
872 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
66 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
109 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
99 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 ...
3
votes
4answers
978 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
78 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 ...
-5
votes
1answer
89 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
264 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
427 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
873 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
503 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
179 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 ...
2
votes
2answers
2k 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
142 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
3answers
1k 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
40 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 ...
-3
votes
5answers
153 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
82 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 ...
2
votes
2answers
77 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
75 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
75 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
814 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 ...
7
votes
7answers
8k views

Why do beginners still write SQL? Why do we not push them to use a higher level abstraction? [closed]

Every day I see SO questions with some variation on this theme (C#-ey pseudocode) method MyButtonClickHandler(){ string sql = "SELECT * FROM table WHERE column = " + myTextBox_Text sqlcommand cmd ...
1
vote
1answer
159 views

All images on a single table. Is a good idea?

I'm making the tables for an application, and I need many tables with images. Instead of storing the BLOBS on each table, I'm thinking on storing all the images (BLOB) on a single table, to unify the ...
9
votes
2answers
3k views

How to safely run database migrations with multiple app instances?

We have an application that has a mix of both fast (< 1 second) and slow database migrations (> 30 seconds). Right now, we're running database migrations as a part of CI, but then our CI tool has ...
5
votes
5answers
391 views

How can I store information with varying structure?

I'm having trouble coming up with a name for this problem, but I feel like its been solved before. I think my naiveté is preventing me from typing the right words into Google to get the results I need....