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
2answers
326 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
224 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
673 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
53 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
123 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
390 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
274 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
640 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
91 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
83 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 ...
1
vote
3answers
334 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
98 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
584 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
295 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
662 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
150 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
3k 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 ...
87
votes
13answers
30k 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
181 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
58 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
158 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
512 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 ...
2
votes
0answers
193 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
4k 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
910 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
89 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
175 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
438 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
6k 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
990 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
154 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
168 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 ...
4
votes
1answer
366 views

Trigger on primary key instead of identity

I have the following problem- About ~60 tables on SQL that some have foreign key to each other,primary key as identity had to change to something else with logic. It was replaced by triggers that ...
3
votes
2answers
1k views

Tidiest method for executing a large SQL query in C# [closed]

I have a SQL query that is around 50 lines long. I'm wondering what would be the be the most maintainable and readable method for executing this, as it looks quite large. So far, I have thought of ...
6
votes
5answers
735 views

Data layer without ORM - how to work with it in efficient way?

I work on a big .NET project. It started more than 10 years ago, there is no ORM (NHibernate or EF I mean) and there is no chance to migrate to some widely used ORM (and for complex SQL queries ORM ...
6
votes
2answers
5k views

What kind of unit tests should be written for a call to a database update function?

I'm dealing with a lot of functions like the one below and I'm unsure of what kind of unit tests should be written for them. public void UpdateEmployeeClockIn(int employeeId) { var sql = string....
3
votes
3answers
750 views

Best Practice regarding table schema

We are discussing in team regarding best practices. For eg. there are three tables Users Groups Teams A user will create a request to join a Team or Group or to follow a Private User. A request ...
18
votes
5answers
4k views

Reason to prefer RIGHT JOIN over LEFT JOIN

If I understand correctly, every RIGHT JOIN: SELECT Persons.*, Orders.* FROM Orders RIGHT JOIN Persons ON Orders.PersonID = Persons.ID can be expressed as a LEFT JOIN: SELECT Persons.*, Orders.* ...
1
vote
0answers
81 views

Creating an autoupdate for my program and its database

I've already an offline updater as a download but I want to make it automatically. It's a client/server software and a ms sql database. 3 parts have to be updated: update of a proprietary software (...
5
votes
3answers
1k views

How to enforce the mandatory participation constraint at the many end in a one-to-many relationship?

Say I have the following ER diagram: Enforcing the mandatory participation constraint at the one end is easy, I simply make the foreign key (school_id) in Student NOT NULL. But how can I enforce the ...
3
votes
1answer
358 views

CQRS: how granular should the queries be?

I have a system that uses CQRS with the Queries written using Dapper. It's worked out well, except that there has been a proliferation of query classes that do almost the same thing. The downside of ...
0
votes
1answer
207 views

Handle database error for API server

Recently I worked with a Restful API server and encountered a problem with handling database error. The basic approach is every time a SQL exception occurred, the server will return "500" to client ...
36
votes
6answers
13k views

Why is SQL the only database query language? [duplicate]

For general-purpose programming there are literally hundreds of programming languages. But for interacting/querying the databases, why is SQL pretty much the only used language?
1
vote
1answer
50 views

Sql Server string Prefix doubt [closed]

I'm learning T-SQL. Why the strings are prefixed with N in every example I've seen? What are the pros or cons of using this prefix? Please make it clear that if my column has datatype as nvarchar /...
0
votes
2answers
798 views

Designing Table To Store Hierarchy

I have a table Company and User like below... Company Table: CompanyID CompanyName etc... User Table: UserID Email UserType CompanyID etc... First I must tell you that, ...