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.

learn more… | top users | synonyms

40
votes
11answers
3k views

Why don't relational databases support returning information in a nested format?

Suppose I'm building a blog that I want to have posts and comments. So I create two tables, a 'posts' table with an autoincrementing integer 'id' column, and a 'comments' table that has a foreign key ...
23
votes
4answers
25k views

why are noSQL databases more scalable than SQL?

Recently I read a lot about noSQL DBMSs. I understand CAP theorem, ACID rules, BASE rules and the basic theory. But didn't find any resources on why is noSQL scalable more easily than RDBMS (e.g. in ...
6
votes
2answers
843 views

Clustering Strings on the basis of Common Substrings

I have around 10000+ strings and have to identify and group all the strings which looks similar(I base the similarity on the number of common words between any two give strings). The more number of ...
10
votes
6answers
22k views

Using a relational database vs JSON objects for event/activity data

I am working on a project where I am trying to decide between using a standard SQL relational database or JSON objects to store data about an event or activity. The project will store data on ...
6
votes
1answer
2k views

How to translate SQL query into REST API requests?

Let's say I have a machine-readable description (such as in WADL, Swagger or RAML) of a REST API that provides interface to a database. My users submit queries about underlying database in form of ...
0
votes
1answer
69 views

SQL Table With A Call To Action?

This is all done in Microsoft Access 2007 and SQL Server. We are creating a way for our users to quickly make notes on a customer. These quick-notes will contain tags that will prompt the user for ...
3
votes
1answer
567 views

Separation of concerns between repository and service in DDD with complex entities

This probably seems as an example of opinion-based question, but I'm actually looking for rationale on how to decide correctly, I believe there is a correct solution that can be backed by solid ...
1
vote
1answer
52 views

MS SQL Server Cell level Encryption options

Recently, i got the task of doing some research into the data encryption at the cell level. I tried using the Symmetric key (by passphrase) & second option i took is Symmetric key (by ...
1
vote
2answers
74 views

Working with local connections instead of global

In recent weeks I became very concerned that the way I've been working with database connections for the past years, is simply wrong. I exclusively create a new connection for each database operation ...
1
vote
4answers
247 views

How to deal with long running transactions?

I'm developing a Content Management System (CMS) and I would like to include a "Save Changes" button. I mean: a user performs several changes in the database, but those changes are still not ...
1
vote
3answers
6k views

Using Autoincrement Primary keys as Foreign Keys

When creating a table, there is an option for us to set the numeric primary key as autoincrement where it's value increases whenever a new data in inserted. This primary number can be used for ...
0
votes
1answer
36 views

Database multiple database query each time

I'm writing a rest service. For each request I use about 4-8 queries. Should I try to refactor those into a single query?
0
votes
3answers
168 views

SQL string field lengths in a db having about 6 different types of addresses, with about 20 markets and environments

I have been advocating lately that all string fields should be ntext / nvarchar(max) - we're using MS SQL Server. The objections seem to be either "it's not a good idea" (without any reason) or "that ...
2
votes
3answers
139 views

Long running DB query with concurrent write at the same time [closed]

We have a big table with a lot of data and a select query which takes 3 seconds to run. However we are a highly concurrent environment and each second we get 100 new records in our database. So ...
35
votes
6answers
65k views

How do I make complex SQL queries easier to write? [closed]

I'm finding it very difficult to write complex SQL queries involving joins across many (at least 3-4) tables and involving several nested conditions. The queries I'm being asked to write are easily ...
3
votes
2answers
351 views

Deep SQL relationships to a C# object model

I have a database with deep table to table relationships for example Clients (one to many) -> ClientData (one to many) -> ClientJob (one to many) -> ClientProcess (one to many) -> ...
13
votes
5answers
9k views

What are questions to test a programmers knowledge of SQL? [closed]

What are questions to test a programmers knowledge of SQL? What is the answer to the question? And what would a lack of an correct answer mean in terms of time likely to understand the concept(s) ...
4
votes
2answers
134 views

Database constraints for a recursive folders structure

In an application, I have a recursive folder structure (like folders in OS X or Windows file system). Each folder can contain three kind of things: Other folders (hence the recursive structure) ...
1
vote
1answer
395 views

Implementing Bulk Update feature and its trade offs

The feature to implement is to allow a user to select items and apply data update in bulk. It is very similar to JIRA's ability to bulk update a list of selected issues. In my case: I iterate over ...
2
votes
1answer
316 views

Is Neo4j graph database sufficient to itself for all query types? [closed]

Neo4j eases relationship traversals => especially useful for a social network for instance. But are there any scenarios when it's advised to associate an additional relational database (or other ...
2
votes
3answers
193 views

automatic database fill up in sql script tests

The problem I am working on an application with ~ 1000 tables in a sql server database. I am having a recurring issue with a sql script. The script is used by the consulting team to "clean" some ...
23
votes
3answers
13k views

self referencing tables, good or bad? [closed]

Representing geographical locations within an application, the design of the underlying data model suggests two clear options (or maybe more?). One table with a self referencing parent_id column uk - ...
3
votes
1answer
159 views

How do SQL transactions return immediate results?

I am using transactions to do bulk insert/updates. This is my little test loop: $now = date('Y-m-d H:i:s'); for ($i=0; $i<60; $i++) { $db->insert($cfg['ps_manufacturer'], array( ...
8
votes
4answers
32k views

Best practice to query data from MS SQL Server in C Sharp?

What is the best way to query data from a MS SQL Server in C Sharp? I know that it is not good practice to have an SQL query in the code. Is the best way to create a stored procedure and call it ...
1
vote
4answers
561 views

What is the “correct” way to store functions in a database?

Note: Yes, I know that storing functions in databases should be punishable by law. We are developing a financial web application using PostgreSQL, Sinatra and AngularJS. As you may have guessed, a ...
0
votes
3answers
321 views

Why does databases use text? [closed]

This might be a little dumb question but why do we save text in the db instead of something smaller? Couldn't there be some other way to store data in the db like in a compressed form, and then have ...
1
vote
1answer
209 views

Why do I need to use recursion on the classic employee manager database relationship?

Take the data from the Oracle scott database. I have modified it to have multiple levels of management from the original, select * from scott.emp order by mgr desc; empno ename job ...
0
votes
1answer
170 views

What is the problem will happen if I create auto generate ID without using mysql auto increase of mysql table by my self?

I would like to ask you a question about MySQL AUTO_INCREMENT. I already created my own function to generate AUTO_INCREMENT when the user inserts any data input a table in MySQL. This function works ...
6
votes
1answer
481 views

Clean Architecture - How to go from “Database Driven” to “Independent of Database” [closed]

I am looking for some clarity and hopefully some advice on writing clean architecture for a large system. My Companies "Web Solution" is +-10 years old, my job is to rewrite it. It is written across a ...
-1
votes
1answer
92 views

behaviour of SQL BETWEEN operator [closed]

This is kind of philosophical question... Today i spent way too much time to debug a method which was generating a SQL command. I figured out the error was because the code was generating something ...
21
votes
4answers
3k views

Why not just make non-parameterized queries return an error?

SQL injection is a very serious security issue, in large part because it's so easy to get it wrong: the obvious, intuitive way to build a query incorporating user input leaves you vulnerable, and the ...
3
votes
1answer
6k views

Automatically adding data from JSON to SQL

I'm really new to this sort of stuff; my background is in C# mainly. Could anyone point me in the right direction? I need to set a script to automatically parse some JSON into an SQL database ...
2
votes
1answer
125 views

Time attendance module design

I am working on time attendance module and I managed to read the data from the timing machine in the following format: Id CheckIn Type Status 0000142 5/15/2015 6:00 PM 2 OK ...
0
votes
1answer
89 views

Optimizing instant notifications

Scenario To deliver instant notifications to the client, I'm sending an AJAX request to a PHP page that checks if there are new notifications: if there are, it outputs them, otherwise it sleeps for ...
1
vote
1answer
123 views

Web framework in pure SQL

I heard that SQL is Turing complete language (for example: http://stackoverflow.com/a/7580013/2604170) I am just curious if would be possible to create independent web framework like Ruby on rails or ...
21
votes
7answers
3k views

Why is filesystem preferred for logs instead of RDBMS?

Question should be clear from its title. For example Apache saves its access and error logs in files instead of RDBMS no matter on how large or small scale it is being utilized. For RDMS we just have ...
9
votes
9answers
4k views

Why don't RDBMSes return joined tables in a nested format?

For example, say I want to fetch a User and all of his phone numbers and email addresses. The phone numbers and emails are stored in separate tables, One user to many phones/emails. I can do this ...
0
votes
0answers
1k views

Why does convention say DB table names should be singular but RESTful resources plural?

It's a pretty established convention that database table names, in SQL at least, should be singular. SELECT * FROM user; See this question and discussion. It's also a pretty established convention ...
2
votes
1answer
573 views

What is the best way to store a formula in a database for a table driven formula? [closed]

So I am changing my code over to a table driven formula system to make it sustainable in the long term. So basically I have 5 foreign keys that will get me to a single formula to use. However I'm ...
1
vote
2answers
155 views

Partial Update of Database Table

I have a database table with numerous fields including "category". The source of this table is an xml file which the system receiving occasionally. Each xml file contains data for one category. The ...
1
vote
1answer
111 views

Is it a good practice to write distinct queries in a single stored procedure based on input parameters

I am going through the stored procedures written throughout our project and see that multiple distinct queries are written inside a single stored procedure and are called by passing different values ...
2
votes
1answer
100 views

Best practices for scope of returned data per stored procedure

I have the following situation: table1 ------------ ID Name Param1 Param2 Param3 RegistrationID 1 Test Value1 Value2 Value3 101 table2 ------------ RegistrationID LangID Value 101 ...
1
vote
1answer
152 views

What's the quickest and most maintainable way to implement SQL querying of disparate C++ variables?

I've recently joined a team working on radar observations. The team has access to a repository of software (written in C++) used for dealing with these observations, but their work is focused towards ...
4
votes
10answers
10k views

Is executing SQL through a WebService a really bad idea?

Typically when creating a simple tool or something that has to use a database, I go through the fairly long process of first creating a webservice that connects to a database then creating methods on ...
1
vote
2answers
161 views

Is there a difference between SQL INSERT patterns

SQL inserts are usually documented like so: INSERT TableName (Column1, Column2) VALUES ('Value1', 'Value2') However, in TSQL it's also possible to write the (seemingly) same statement like so: ...
11
votes
4answers
12k views

Database History Table / Tracking Table

Currently I want to structure a tracking/history table like this: PrimaryKey - ID OtherTableId - fk fieldName - name of the field its tracking OldValue NewValue UserName CreateDateTime So ...
156
votes
19answers
58k views

Why is naming a table's Primary Key column “Id” considered bad practice? [closed]

My t-sql teacher told us that naming our PK column "Id" is considered bad practice without any further explanations. Why is naming a table PK column "Id" is considered bad practice?
2
votes
3answers
190 views

At which point using string-based enums can become a real problem?

This is a direct follower to this question. An answer given to that questions says (my understanding), that nowadays using string-based enums instead of integers isn't a much problem, because hosting ...
3
votes
4answers
2k views

Database is performing slow, even all the tables are having normalization

An interviewer asked me this question: Tables are created with appropriate normalization rules, However the database is performing slow. [Ie.: The select, insert statements are taking time to do ...
1
vote
0answers
33 views

Adding fields a good use case for EAV? [duplicate]

Is being able to add fields quickly to a system a good use case for EAV? What would be the frequency or number that would justify using an EAV model?