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
1
vote
4answers
111 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
725 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
458 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
939 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
161 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 ...
97
votes
14answers
38k 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
278 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
99 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
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 ...
1
vote
1answer
196 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
555 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
260 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
6k 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
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 ...
4
votes
1answer
98 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
307 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
587 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 ...
5
votes
1answer
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 ...
4
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
160 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
64 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
173 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
437 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
2k 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 ...
7
votes
5answers
1k 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
7k 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
863 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 ...
23
votes
5answers
8k 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
131 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
2k 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
384 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
279 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 ...
39
votes
6answers
16k 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
51 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
956 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, ...
-1
votes
2answers
165 views

Relational or non relational database for my described application?

I want to design and implement a mobile application in which: Every user who have already registered is able to add new news (including title: text plus a picture, and content: long text, pictures or ...
4
votes
1answer
345 views

Preferred approach to a matching process

I am currently in the process of putting together a matching algorithm. The matching process is as follows: Query data is used to perform a "lookup" on a set of reference data in order to determine ...
3
votes
1answer
255 views

Is it normal to have a SQL heavy backend for a web app's REST API?

I'm building my first PHP web app with AngularJS as the front end and utilizing Slim to create a REST-like API for the web app to interface with. I'm finding that the majority of my code I'm writing ...
4
votes
2answers
184 views

Why are dependencies typically reversed when representing complex objects as relational tables?

I'm going through some sort of phase where I over analyze and second guess every single decision I make when attempting to write software that has preventing me from getting anything done. I recently ...
2
votes
3answers
7k views

In-memory data collection object(s) vs database

I have a couple of questions about the practicality of developing a WPF application that loads data into memory from a text file and then manipulates the resulting object(s) instead of transactions ...
0
votes
3answers
8k views

Best way to query data from database and then modify it

I'm working on a software using VB.Net which retrieves string packets through a TCP socket. The problem is it receives hundreds of packets per second. For each incoming packet the software should ...
41
votes
6answers
12k views

Why not return dates as a string from the database?

In a typical web application, dates are retrieved from the database layer strongly typed (e.g. in c# as a System.DateTime as opposed System.String). When a date needs to be expressed as a string (e....
1
vote
3answers
187 views

How can I model IS-A relationship on a ledger, when having different classes of products?

This is a question on how to model database entities and relationships when it comes to storing persistent data and dealing with differently-handled types of products. Use Case Company sells various ...
1
vote
3answers
192 views

Is there a design pattern which describes separated join-only views and format-only views

When I'm building views in SQL, I tend to do one of the following: Views that contain logic and criteria for selecting and joining records. I.e. "what records am I interested in and how do they fit ...
59
votes
14answers
14k views

Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?

The way I see it, SQL injection attacks can be prevented by: Carefully screening, filtering, encoding input (before insertion into SQL) Using prepared statements / parameterized queries I suppose ...
4
votes
1answer
3k views

CRUD without an ORM

I am putting together an web app with relatively complex but standard database relationships. I notice that anytime I use an ORM, in python/ruby/php etc, in general, a lot of queries are generated. ...
3
votes
1answer
137 views

T-SQL Development debate, which is the correct approach

The company I work at uses stored procedures (and a home-grown ORM) exclusively for all database interaction, selects, updates, deletes. We have numerous procs that will insert and/or update and/or ...
5
votes
3answers
353 views

How to avoid mixing surrogate key with the rest of the data

Suppose you have a very simple CRUD where you are just storing a simple tuple-like type of data, but its natural primary key is cumbersome for the user to remember (very long, difficult to write, etc.)...

1
3 4
5
6 7
15