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

2
votes
4answers
314 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 ...
-4
votes
0answers
22 views

Should contentType be in my ajax? If yes how to fix it [on hold]

I been trying to figure this one out but i don't seem to find the error but in my script My script $('#Bshift').click(function(){ var isValid=false; isValid = validateForm(); if(isValid) { var ...
-1
votes
0answers
26 views

Background service to act as ad-hoc API? [on hold]

I've been given a new project at work, and I'm fairly new so I'm not entirely sure where to start. My boss uses two different applications to track his IT Service work. He uses one third party ...
0
votes
3answers
272 views

Why does databases use text? [on hold]

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 ...
3
votes
1answer
369 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
0answers
75 views

How Should I Design Models With Deep Relationship Structures?

I am designing a web application in ASP.NET MVC, which uses ADO.NET for the database access. In the past, I generally have put together a series of classes each of which, maps to one table in a ...
1
vote
1answer
162 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
81 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 ...
5
votes
1answer
92 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 ...
0
votes
1answer
61 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 ...
6
votes
2answers
626 views

What is the best way to format complex SQL queries in Node?

I am using node-mysql to query a MySQL database via Express and Node. My queries are getting fairly complex and I want to format them across multiple lines. However, the only way I could accomplish ...
20
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
600 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 ...
1
vote
1answer
84 views

Best approach to setting up test and production environments for a new project [closed]

We're a very small team of not very experienced programmers who just started a decently big project from scratch. We're using Visual Studio 2015, SQL Server 2008 and the Entity Framework. The company ...
3
votes
1answer
5k 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
35 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
53 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
94 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 ...
19
votes
7answers
2k 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
3k 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 ...
1
vote
0answers
93 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
111 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
50 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
0answers
40 views

Storing/caching SQL query

If I have a very long-winded SQL query (that has multiple subqueries), but the result of which is unlikely to change from hour to hour (or even day-to-day, or possibly even week-to-week) is there some ...
1
vote
1answer
51 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
61 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 ...
-5
votes
1answer
60 views

confused with ms sql nomenclature [closed]

Question 1. What is the language used in Microsoft SQL Server? Question 2. I get confused with the name Microsoft SQL Server since its a RDBMS why its name ends as "Server". Because server is used to ...
1
vote
1answer
94 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
8k 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
133 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
9k 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 ...
144
votes
19answers
47k 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
133 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
1k 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
32 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?
2
votes
6answers
748 views

What is faster? Using REST API or querying a database directly?

What is faster performance wise? Creating a REST API and having your web app use the REST API to do all interactions with your database OR querying your database directly (i.e. using whatever typical ...
1
vote
1answer
84 views

Knowing when a SQL table is done updating before Querying

I am using a self hosted windows service WEB API as the layer between my Windows 8 App and a SQL Server Database because Windows 8 apps can't directly connect to SQL server like win forms or pretty ...
4
votes
2answers
140 views

Are (basic) SQL queries semantically equivalent to Higher Order Functions?

Is SQL basically a domain specific instance of map + fold + filter? It seems to me that the following SQL: SELECT name FROM fruits WHERE calories < 100 is just syntactic sugar for the ...
2
votes
0answers
175 views

The best way to convert Dynamic SQL to C#/Linq while joining multiple tables

I am trying to find the best way to convert the following dynamic SQL snippet to C# / Linq. The question is complicated by the use of multiple tables within the dynamic SQL. I've examined the ...
4
votes
1answer
85 views

Status Bar Timetracking Design

I'm currently developing an application in ASP.NET using SQL as the backend database. In my header, I have a status bar that displays the current status of the user and a timer that shows how long ...
38
votes
11answers
2k 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 ...
33
votes
11answers
3k views

Is there any material difference between queries joined by WHERE clauses, and queries using an actual JOIN?

In Learn SQL the Hard Way (exercise six), the author presents the following query: SELECT pet.id, pet.name, pet.age, pet.dead FROM pet, person_pet, person WHERE pet.id = person_pet.pet_id ...
0
votes
1answer
66 views

How to handle rating? [closed]

I want to implement like/dislike system, but I don't know how to achieve best performance. I have to tables: blog posts and comments that need rating. Should I store all data in one rating table? Or ...
0
votes
0answers
16 views

One Enum vs Multiple Enums [duplicate]

I am developing an application where a user submits a mission and other users accept the mission. Pretty simple. I want to keep a track of the mission progress status and store it into a database. ...
3
votes
2answers
470 views

How do you unit test \ use TDD methods for ETL's and reporting projects?

ETL projects are projects created using an ETL (Extract - Transform - Load) tool such as SSIS, PowerCenter,etc These typically involve reading data from an external source, loading it to a staging ...
2
votes
3answers
134 views

Database view performance - good practice?

Often times at work we opt to create views in the database to expose the data that we want to work with instead of building some monster query in our code. Being somewhat new to this field my ...
1
vote
1answer
68 views

SQL - Designing a Threshold value table

I need to design an SQL table to be used for "threshold" or special values, which in general would have the following properties: - key - value - comparer (less than and greater than as a start, ...
2
votes
1answer
70 views

Storing values in SQL whose types are determined in runtime

I have come across a need of storing runtime determined values in a SQL database. For example, there is a GUI where a user can add new editable fields. So the user adds a field Name, chooses the ...
1
vote
1answer
100 views

When the result set doesn't matter should Left Join or Inner Join be used

When you have a query where you're joining from a non-null foreign key to the foreign table it doesn't matter if you use LEFT JOIN or INNER JOIN from a result standpoint (since there won't be any ...
4
votes
2answers
942 views

How deeply programmer should know about internals of database engine? [closed]

I am learning SQL Server 2012 along with C#. In the past, I took database class in college, and have good grasp on SQL language and can write queries. However, looking at SQL Server documentation (or ...