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.
745
questions
0
votes
3
answers
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,489
-1
votes
1
answer
74
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 ...
- 1
-2
votes
5
answers
211
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 ...
- 149
1
vote
1
answer
148
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 ...
- 29
2
votes
2
answers
83
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)
...
- 321
1
vote
1
answer
106
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) ...
- 123
1
vote
1
answer
88
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 ...
- 113
2
votes
1
answer
2k
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 ...
- 131
8
votes
7
answers
9k
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 ...
- 685
3
votes
1
answer
425
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 ...
- 51
12
votes
2
answers
7k
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 ...
- 313
5
votes
5
answers
573
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....
- 59
1
vote
1
answer
3k
views
SQLAlchemy self referential tables explained
I encountered in a book some explanation how self referential tables in SQLAlchemy (Python) work, as an example, here is the class Employee with a manager_id:
class Employee(Base):
__tablename__ =...
- 131
-2
votes
2
answers
1k
views
What does it mean to "wrap" a stored procedure?
My manager has asked me to "just wrap the stored procedure".
The requirements are to create a microservice that will do the following:
Accept parameters from the user.
Pass those parameters to the ...
- 495
1
vote
3
answers
5k
views
Which is a more efficient way of storing data in a database using Objects or strings (Database design)
Which method is better to store the data in a database and why?
Here is a restaurant example. I'm using a SQL database and JSON to communicate with the database.
Imagine each row is a column in a ...
- 19
0
votes
1
answer
338
views
MySQL linter: Letting an installed python package Integrate with a user-written python module
I'm working on a new style of database migration system that is coming along nicely. There is one last aspect of it that I want to finish, which is to enable MySQL linting. For background, this is a ...
- 386
8
votes
8
answers
813
views
Is there a compelling reason why columns in SQL are nullable by default?
As a CS student, I've learned a decent number of programming languages over the years, most of which have had some concept of a "nullable" or "optional" type. Note that I'm not talking about null ...
1
vote
2
answers
66
views
Proper way to track ranking evolution of some db records
I will have a database of products where each record will have a number associated with it, let's say, the number of "sales".
I will periodically update the database so each "sales" field for each ...
- 231
2
votes
3
answers
253
views
Using incrementing primary keys on real world data
Is it bad practice to use an incrementing primary key to refer to a real world field, such as an id number for an employee?
Suppose that 1 is a valid id number for an employee. I am really tempted to ...
1
vote
2
answers
507
views
NoSQL or SQL for my situation? [closed]
I'm sure this has been asked multiple times but for me it's more specific as I know what I'm building. I wrongly posted this to stack-overflow (now removed) so I'm hoping this is the best place to ask....
- 37
0
votes
3
answers
311
views
Which database strategy should I choose?
I am going to log some events happening in my program. My program is working on a database that I cannot change (add table). These events can be stored in a single table with no relations.
I would ...
- 529
13
votes
4
answers
763
views
About my database date datatype crusade: Valid? Worthwhile? Does anyone else feel it?
I spend a lot of time answering SQL questions over on SO. I frequently come across queries of this ilk:
SELECT * FROM person WHERE birthdate BETWEEN '01/01/2017' AND '01/03/2017'
SELECT * FROM ...
- 685
3
votes
1
answer
132
views
Why do the column expressions come before the table references in SELECT queries?
It strikes me as weird that the column expressions (everything between SELECT and FROM) in an SQL SELECT statement come before the table references, when the column expressions depend directly on the ...
- 507
0
votes
4
answers
2k
views
Best way to track user changes
I'm developing a webapp with users and roles, and I need to keep track of any changes that are made, so that I know when and who has changed a user role.
I have my user table:
CREATE TABLE user(
...
- 3
-1
votes
1
answer
89
views
.NET Sort concurrent requests into a queue
I asked this at Stack Overflow but seeing as it wasn't so much a programming issue as a theory issue it wasn't well received.
I have a program that issues a unique ascending code every time one is ...
3
votes
3
answers
802
views
Row level access in a monolith application and in microservices based application
I would like to get an advice on how to tackle such a problem.
Currently we're using SQL Server as our database and the whole application is a fat, juicy monolith which we'd like to break down. There'...
1
vote
4
answers
115
views
Backing up database tables before nightly import
My system is importing data from a number of external sources on a nightly basis into staging tables in my local DB. The process backs up the relevant table (by copying all data to a replica table ...
- 149
1
vote
2
answers
762
views
What problems arise from writing PHP for IIS/SQL Server rather than Apache/MySQL?
I'm a .NET developer, currently writing an ASP.NET site hosted on our local servers with Windows, IIS, and SQL Server. We're speaking to a company in India about hiring a developer for a different ...
- 131
2
votes
1
answer
529
views
Scaling resolution of time series data
I built an app that display time series data. The display is very similar to a yahoo finance chart (ie https://finance.yahoo.com/quote/AMZN/). I am using Postgres to store the data because that was ...
- 121
5
votes
3
answers
1k
views
Stored procedures or ORM in web?
I am working in a Peruvian company that develops desktop accounting software(.net C#).
We have many clients (companies) each customer can create several companies, in addition there is a new database ...
-5
votes
1
answer
173
views
What should I use columns for, and what should I use rows for in SQL? [closed]
In SQL, what's the standard convention for what rows and columns should be used for? For instance, if I need to make a table of users and their data, which axis (rows or columns) should be the users ...
- 211
46
votes
9
answers
4k
views
What do relational databases gain by setting a predefined data type for each column?
I'm working with an SQL database right now, and this has always made me curious, but Google searches don't turn much up: Why the strict data types?
I understand why you'd have a few different data ...
- 1,007
99
votes
14
answers
40k
views
Is it considered an anti pattern to write SQL in the source code?
Is it considered an anti pattern to hardcode SQL into an application like this:
public List<int> getPersonIDs()
{
List<int> listPersonIDs = new List<int>();
using (...
- 6,973
-1
votes
2
answers
318
views
Using NoSQL as MQ and for maintaining state
Currently we have an application that doesn't scale well because of multiple concurrent updates to a table which gets locked. The tech stack is ASP.NET and backend is in SQL Server.
To avoid database ...
- 99
0
votes
1
answer
111
views
When should I create database records for each instance of a class in my web app?
I'm building a web application in PHP to handle attendance tracking at my small English school. I've got the database up and running with PostgreSQL and I've been importing data and working out the ...
- 9
2
votes
3
answers
62
views
Verbose specification of exact Table or Implied
Consider the following two queries in SQL (Transact SQL):
UPDATE [database_name].[dbo].[table_name]
SET [database_name].[dbo].[table_name].[field_name] = @value
WHERE [database_name].[dbo].[...
- 131
2
votes
1
answer
3k
views
How to properly serve large video files stored outside a MySQL database
I'm trying to build a Java application that connects to a remote database that stores and retrieves video files that can be over 200MB. I could store/retrieve these files in MySQL directly as ...
- 129
2
votes
1
answer
209
views
Need to match database tags using a boolean expression
I have a table, let's call it Widgets, a Tags table with about 30 tags, a WidgetsTags table that joins each widget with any number of tags, and a table called LandingPages, the landing pages are for ...
- 602
2
votes
3
answers
569
views
Using MongoDB for not scalable application?
I have mainly worked on RDBMS databases. Recently studied MongoDB and liked where we don't have to define schemas upfront. So application developers straightaway can start writing the code. Major ...
- 457
3
votes
0
answers
285
views
How to model access rights in MongoDB versus SQL
I've started to implement a full MEAN-stack application (MongoDB, Express, Angular2, node) and I am struggling with the data design for the access management in the MongoDB. In my old thinking tracks, ...
- 131
4
votes
8
answers
7k
views
How to make ability to order records in DB?
I need to make ability to reorder records, storing in DB (I use MS SQL, but seems it's no matter).
I see possible 2 solutions:
Add Order column. Then if we want to reorder 2 records, we need to ...
- 205
7
votes
4
answers
2k
views
Database structure to save 'tables'
I'm working on a project in which a user can create tables to save some data. What would be the best way to implememt this? Right now I'm doing it like this:
Table 'tables' - contains stuff the user ...
- 79
4
votes
1
answer
103
views
Need help with system migration plan
I need to rewrite old ASP.NET web forms VB application to new ASP.net mvc in c#.
Problem is that most of the old application logic for data inserting retrieval is written in stored procedures, which ...
- 545
2
votes
1
answer
326
views
Algorithm suggestions for handling CRUD Soft Deletes for delete and restore with multiple owning models
I have an active record based ORM with soft deletes (Laravel Eloquent to be specific) in an SQL database.
The 'soft delete' behavior that is built in to the ORM is:
If the record is not deleted, then ...
- 145
1
vote
2
answers
605
views
clarification about DAL and MySQLi functions in PHP
Up to now, I was considering MySQLi functions in PHP one example of DAL. And I initiated a simple project to publish in an article with the title "starting with PHP and DAL and MySQL"
But when I made ...
- 119
5
votes
1
answer
8k
views
How to perform a 2 way sync from many client applications to central server?
I have many client applications that sync up to a central server.
I am using C#, MS SQL server, Win Forms, ASP.NET MVC + API.
I need to learn/figure out how to perform a 2 way sync for data changes ...
- 161
4
votes
1
answer
1k
views
Multiple databases or one single database
I've seen a few answers on this already but nothing that really applies to my particular situation.
I'm going to be building a mobile application, so primarily phones, tablets etc. Probably using ...
- 43
0
votes
1
answer
164
views
What are the drawbacks to SQL in JSP, PHP, or other server-side scripting?
I'm working on a code base that dates back at least 15 and possibly as much as 20 years, where the older code is still functional but includes thousands of JSPs full of Connection, PreparedStatement, ...
- 251
0
votes
0
answers
73
views
Is data binding in an SQL RDBMS a fruitful endeavour?
I've found that a common task for web applications I've built is to react to a change in the state of the database. That is, any user interaction that affects the persistent state may change views ...
- 507
5
votes
2
answers
178
views
What strategy should I employ using C# to solve this use case?
So this is all hypothetical and theoretical really. This isn't a real system I'd have to architect, but its a good question.
A company stores and reproduces contracts. Contracts can have many ...
- 161