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

0
votes
0answers
106 views

Advice on program performance after changes

So, the situation is like this: For the past two years, I have implemented a class in c# which is used to filter different fields from different tables in database (SQL), for reporting purposes. Now ...
0
votes
0answers
50 views

Use vb.net sql reader as a Delegate to gather Data

Is there a way to use a sub to create a loop, but change the content of the inside of the loop? I don't know what I don't know. I keep looking at this code and I have a feeling that it's possible. ...
0
votes
1answer
160 views

Designing Pricing table (RDBMS agnostic)

I'm designing a pricelist table for my database. It will include Customer, Model, Start_date, End_date, Price, Currency, RRP When I update a new pricelist which is sent every now and then (maybe ...
42
votes
8answers
6k views

Is the use of NoSQL Databases impractical for large datasets where you need to search by content?

I've been learning about NoSQL Databases for a week now. I really understand the advantages of NoSQL Databases and the many use cases they are great for. But often people write their articles as if ...
4
votes
2answers
91 views

How to represent a collection with capacity in a database?

Concerning normalization of a one to many relationship, I am not sure how to represent a collection with a specified capacity in a database. I can think of two ways to represent an entity A with a ...
3
votes
3answers
116 views

Relational table design for data ownership

Setup (schema simplified for clarity) Suppose I have a table called resource: +---------------+--------------+------+-----+ | Field | Type | Null | Key | +---------------+-----------...
1
vote
1answer
82 views

How to handle multiple “valid after {date}” in a performant way

We work on an Application which displays a duty roster and also some other DateTime related things. So my Database is full of Relations like in this small Example: This results in big ...
3
votes
1answer
102 views

Django Migrations: Binds data to code?

If you have custom code for models (custom validators and custom fields are examples), Django Migrations will import them directly into the migration files. An example: file web/models.py def ...
1
vote
1answer
60 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 Certificate)....
1
vote
2answers
76 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
260 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 available....
0
votes
1answer
39 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?
2
votes
3answers
168 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 let'...
3
votes
2answers
415 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) -> ...
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 ...
4
votes
2answers
146 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) ...
2
votes
3answers
220 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 ...
3
votes
1answer
167 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( '...
0
votes
3answers
328 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 "...
0
votes
1answer
180 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
525 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
vote
1answer
217 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 ...
-1
votes
1answer
106 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 ...
2
votes
1answer
155 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
99 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
132 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 ...
1
vote
0answers
2k 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
760 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
172 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 ...
2
votes
1answer
101 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
130 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 ...
1
vote
1answer
162 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 ...
2
votes
3answers
197 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 ...
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?
3
votes
7answers
10k 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 ...
5
votes
2answers
251 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 ...
3
votes
0answers
492 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
96 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 ...
1
vote
1answer
200 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
17 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. ...
2
votes
3answers
340 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 ...
2
votes
1answer
89 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
115 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 ...
6
votes
3answers
383 views

Do common relational database systems work 'out of the box'? [closed]

I'm quite new to software development and whenever I come across small projects which involve storing (relational) data I always ask myself if something like a micro-blog (or any other project with ...
12
votes
2answers
2k views

Why store flags/enums in a database as strings instead of integers?

I've been browsing SQL dumps of some famous CMSes, including Drupal 7, Wordpress (some quite very old version), and some custom application based on Python. All of these dumps contained data with ...
1
vote
1answer
107 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 ...
1
vote
2answers
41 views

Using a masterID in sql to merge different item types

I am designing tables in sql for a small ticketing system. One of the demands is that a user/agent can post a ticket to either a department or an agent. Now if I have a child parent table where each ...
0
votes
1answer
66 views

Do Temporary tables have any form of index in SQL?

Consider a simple query like this: SELECT * FROM DATA JOIN ( SELECT * FROM DATA )TEMPORARY_DATA ON TEMPORARY_DATA.DATA_IDN = DATA.DATA_IDN What is the performance of this? Is it O(n2) because ...
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: ...