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.
5
votes
2answers
152 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
152 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 ...
0
votes
0answers
18 views
Best practices for Anorm like framework?
I'm working on a small enterprise application where we are using Scala and a custom JDBC wrapper that behaves a lot like Anorm - ie we have queries like
"SELECT BLAH FROM ABC WHERE A=? AND B=?"
...
13
votes
4answers
577 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.*
...
0
votes
0answers
59 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
96 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 ...
2
votes
1answer
84 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
2answers
105 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 ...
31
votes
6answers
4k 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
40 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
40 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
100 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
0answers
30 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 ...
2
votes
1answer
127 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
118 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 ...
1
vote
3answers
214 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
200 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 ...
39
votes
6answers
7k 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
1answer
99 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
122 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 ...
55
votes
13answers
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
158 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
96 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
106 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
vote
1answer
137 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
53 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
56 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 ...
1
vote
3answers
234 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
113 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, ...
138
votes
17answers
16k 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 ...
1
vote
1answer
109 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
...
7
votes
1answer
120 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
68 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 ...
-4
votes
1answer
57 views
Select rows from table1 where message_id doesn't exist in table 2? [closed]
I have a table named messages, which has a column named id (and other non relevant columns). Then I have another table named messages_data which has a column named message_id (and other non relevant ...
2
votes
3answers
160 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
148 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 ...
0
votes
0answers
44 views
Hybrid sql / nosql
I have a problem that doesn't seem to have an immediate fix and it confuses me.
My main application stores data in mysql:
Users
Websites (metadata)
Pages (metadata)
The actual pages ...
1
vote
1answer
131 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-&...
0
votes
1answer
59 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
115 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
165 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
164 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 ...
1
vote
2answers
34 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
99 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
217 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
50 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
346 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
188 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 ...
3
votes
2answers
180 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
381 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.
...
