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
3
votes
1answer
360 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
213 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
14k 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
838 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
157 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
264 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
237 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
175 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 ...
0
votes
3answers
6k 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
7k 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
11k 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
177 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
188 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
13k 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 ...
3
votes
1answer
2k 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
126 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
278 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.)...
3
votes
1answer
2k views

Is indexing foreign keys a good practice?

Looking at DB tables created by a different developer I have noticed that whenever a table had a forein_key_id field/column, it was always an INDEX/KEY. I am not sure if it was manually created, or ...
2
votes
2answers
79 views

Labeling the multiplicities in class-table mappings

Trying this again but with a slightly different approach. My previous thread was here Can anyone explain this one-to-one, one-to-many, many-to-one, many-to-many concept with respect to ORMs? but ...
0
votes
1answer
509 views

REST API - Eager loading nested resources

I would like to know if I am correctly eager loading nested resources in my API. In my app, companies can have many technologies, and tools. The relationship is stored in the join tables ...
2
votes
3answers
4k views

How to design ER diagram to allow for retrieving of product option pricing data?

I'm looking for a way to organize database tables when it comes to handling product option pricing retrieval and also storing pricing history for my use case. What I have I have a custom-made ...
1
vote
1answer
139 views

Verfiy physical device on web service

The problem is quite simple really. I'm trying to create a site, where the user can add a physical device to a database. Every device has its own unique id, which is stored in the database. However, ...
193
votes
17answers
58k views

Is it good practice to always have an autoincrement integer primary key?

In my databases, I tend to get into the habit of having an auto-incrementing integer primary key with the name id for every table I make so that I have a unique lookup for any particular row. Is this ...
2
votes
1answer
3k views

CMS database schema design advice

I am designing a CMS, and would like to start with a simple database schema, with the requirements Two main types of entity,Post and Category For Post, it need to support multilingual and versioning ...
6
votes
1answer
222 views

Replacing Dynamic SQL w/ a Filtering Pipeline?

As part of a work assignment I've long dealt with a giant, dynamic SQL-creating stored procedure that's used for retrieving a bunch of inventory items. This stored procedure accepts a ton of ...
0
votes
1answer
1k views

Single Database Vs Multiple Database on SQL Server

we have web application which stores information in backend SQL 2014. currently the way is set up that central database stores all key information for the customers and then we have seperate database ...
2
votes
3answers
353 views

Java/Android CRUD SQL design

All the SQL in my app... Should I be confining it to its own special class? For example a MyDatabase class that extends SQLiteOpenHelper and implements all the create-tables, defines all the table ...
3
votes
3answers
162 views

How should I query the DB for a specific key without querying every time the feature is called?

We have a large legacy Java-based project, the availability of certain features throughout the application is determined by its corresponding value in a "feature_enabled" table in a SQL database. Much ...
1
vote
1answer
136 views

Pass the parser to the sorter, or pass the sorter to the parser?

I have 2 objects that I'm not very certain what is the proper (or better) way I should use. I have a query builder object that constructs an SQL statement like $qb = new SelectQuery('users'); $qb-&...
1
vote
1answer
2k views

SQL: Empty value list for the IN predicate (literals, not subqueries)

Recently, while writing some Spring/JPA code, I stumbled on an issue I tend to face with from time to time: I passed an empty collection to a repository method that generates an IN expression for the ...
-3
votes
1answer
254 views

Is SQL inefficient since it has to parse through everything?

As you know SQL doesn't have arrays. So if you make a table "article" and wish to have comments on your articles I presume you would make another table "comment". Inside "comment" table you have the ...
2
votes
2answers
496 views

Use one query that fetches redundant data from linked tables or multiple queries and join them in code?

I have three tables with a chain of two one-to-many relationships. There are two ways I can think of of fetching the data based on the primary key of the first table. JOIN each table (or LEFT JOIN if ...
2
votes
2answers
2k views

Is a one-to-none relationship one-to-one or one-to-many?

I am working on an existing database design. There is one table in which each row is created with only half the columns populated (the remainder are initially all NULL, except for an is_populated ...
0
votes
2answers
48 views

what is a better approach in replicating data from table to another? triggers or a third party ETL tool?

We have multiple tables which we need to retrieve data from and dump to one centralized table. Currently what we are doing is running an ETL job made from Pentaho, retrieve the records from the source ...
-1
votes
1answer
150 views

Sql vs nosql important data [closed]

In one of youtubes videos about scalling web sites I found the statement that, however, in term of scallability, noSql is easier and more flexible, though it is better to use SQL for data important ...
-1
votes
2answers
1k views

Why use using-statements when you want to reopen connections? [closed]

In a Winforms project I've been working on recently, I've been dutifully using using-statements for objects that implement IDisposable. I want to reopen a database connection, however because the ...
1
vote
2answers
279 views

'first_name' vs 'name_first' for Grouping Similar SQL Columns

When creating a table with similar bits or data such as names. Is it preferred to have the column name prefixed with a common value? Consider a table to store user information. Looking at the columns ...
8
votes
9answers
393 views

Is it unnecessary to learn the kind of data structures and objects inside sql only because we are using another language to access db indirectly?

Suppose we are using java or python to access the database. Then is it considered a wastage of time and unnecessary to learn the kind of data structures and objects being used inside sql? Please ...
-1
votes
1answer
355 views

What's the best way to populate a new row in a database?

Is it possible to add a new row to a database using DataSet? When I created a new DataSet and dragged the table from the database I could see that in the DataSet methods there was one called insert ...
4
votes
2answers
289 views

Is there some rationale behind SQL's lack of closure for what are standard operations in imperative languages?

Is there some historical or practical reason why SQL (or more specifically T-SQL in my case) does not support the closure property in many areas where many other language families like C do? (That is,...
2
votes
2answers
5k views

How To Design Time Slot Based App

I have got a responsibility of designing an app which allocates time slots for doctors booking. The scenario is like, there'll be entry for doctors and their time slot for each days. For E.g. ...
0
votes
1answer
64 views

sql - How do I add child of child when dependent on parent

I'm building a wpf application which interacts with a sql database. I'm confused about adding children of children in my window. For example.. I just created a fishing trip so my fishing trip doesnt ...
0
votes
1answer
230 views

User, user Role and user Info, data base design problem

I am creating an application that will manage job interviews and help the employers to choose the job seekers based on a ranking system that's based on some user info. I've created a database that has ...
6
votes
2answers
194 views

Seeking benefits of both SQL and binary files in a data storage scheme

I'm looking for some input on how to keep certain benefits of both a binary file and a SQL database in an interesting data storage problem. I have a legacy custom binary file format that is ...
2
votes
1answer
546 views

Creating a service to to run logic / queries and update a table vs. running logic / queries on client refreshes

I'm not sure if this is a thing. I'm sorry about the ambiguous title--I wasn't really sure how to explain it. Basically I have a ASP.NET web page that runs two SQL queries and does some logic to ...
2
votes
2answers
120 views

When do I prefer a local DB over serialization for local data storage?

I recently started developing a small calendar application, in which I can add and view several calendar entries, which contain several data like name, date, alarm settings and so on. Those values ...
-1
votes
2answers
436 views

SQL query - split and run parallely?

I have a master table T1 which has 1000 records and a ColRef a key column. There are lots other tables all linked by ColRef. I have a Stored procedure which takes few hours to process. I want to ...
0
votes
1answer
128 views

How to setup multiple queries with slight differences

I'm writing a page that will show the user statistical data about some of our products. Basically the user can get results between a specific time frame, and also add on other data to lookup by. For ...
3
votes
1answer
400 views

Dynamic form builder with serialised SQL persistence

I have a requirement whereby my users will need to be able to build their own forms. Which they can specify different datatypes: Text DateTime Checkboxes All customisable mandatory/optional. I've ...