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
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
45 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
154 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
86 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
79 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
78 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
974 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
167 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 ...
10
votes
2answers
4k 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
423 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....
1
vote
1answer
1k 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__ =...
-2
votes
2answers
386 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 ...
1
vote
3answers
2k 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 ...
0
votes
1answer
256 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 ...
8
votes
8answers
684 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
2answers
55 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 ...
1
vote
3answers
125 views

using incremeting 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
2answers
411 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....
0
votes
3answers
276 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 ...
13
votes
4answers
653 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 ...
2
votes
1answer
95 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 ...
0
votes
4answers
1k 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( ...
-1
votes
1answer
84 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 ...
2
votes
3answers
408 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
4answers
99 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 ...
1
vote
2answers
646 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 ...
2
votes
1answer
333 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 ...
5
votes
3answers
751 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
1answer
153 views

What should I use columns for, and what should I use rows for in SQL?

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 ...
44
votes
9answers
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 ...
88
votes
13answers
32k 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 (...
-1
votes
2answers
208 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 ...
0
votes
1answer
95 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 ...
2
votes
3answers
59 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].[...
2
votes
1answer
2k 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 ...
1
vote
1answer
162 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 ...
2
votes
3answers
522 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 ...
3
votes
0answers
216 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, ...
3
votes
8answers
5k 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 ...
6
votes
4answers
969 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 ...
4
votes
1answer
90 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 ...
1
vote
1answer
216 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 ...
1
vote
2answers
495 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 ...
4
votes
1answer
7k 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 ...
3
votes
1answer
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 ...
0
votes
1answer
155 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, ...
0
votes
0answers
53 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 ...
5
votes
2answers
169 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 ...

1 2 3 4 5 13