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
1answer
58 views

Why use JSON dataset rather than SQL Query?

A colleague was asking me to explain a system information flow, as they are having problems with the wrong information being presented. It seems that what they have created is a custom built website, ...
1
vote
1answer
1k views

Notify client about changes in database in Java

I'm working on a distributed client-server system that uses TCP. The program's functions is to transfer money to an from the server, but also between the clients, a sort of MobilePay. When one ...
0
votes
1answer
69 views

Relationship between REST APIs and Databases

I've been studying databases and rest APIs lately and I have a question about the relationship between the two. Imagine I have a database with three tables, STUDENTS, ENROLLED, and CLASSES. STUDENTS ...
1
vote
3answers
171 views

Tracking time users spend on my website

We are going to track user engagement (i.e. time spent on website, most viewed part/page of the system, etc). I don't see Google Analytics / MixPanel being able to do this, since we have to analyze ...
0
votes
0answers
21 views

Recommendations for Big Data filter/formula language [closed]

I'm working on a project which deals with terabytes of logging data which all must be preserved in it's original state. On top of this, to make it meaningful to the end user, it needs to have VERY ...
8
votes
4answers
455 views

Is a 25+ table join a code smell all by itself? [closed]

I just think our database design is terrible. Academically it's great! It's in like 85th normal form. Practically, this results in some queries with so many tables, it's basically impossible for a ...
0
votes
0answers
32 views

Best practices for dashboard of near real-time analytics

I’m currently building a dashboard to view some analytics about the data generated by my company's product. We use MySQL as our database. The SQL queries to generate the analytics from the raw live ...
2
votes
4answers
70 views

Keeping a ratio column that's the division between two other columns

I'm about to build a SQL table where I want to store currency orders. That means that I need to store how much I paid for a certain quantity, and the ratio between both quantities. So for example: ...
9
votes
7answers
3k views

What are the advantages to using SQL query builders?

Are there any advantages to using a query builder, rather than using raw SQL? E.g. $q->select('*') ->from('posts') ->innerJoin('terms', 'post_id') ->where(...) vs: SELECT * FROM ...
0
votes
0answers
11 views

SQL data model for nested milestones/timframes

I'm currently weighing different approaches to a data model and I'm curious what others would do. The main nugget of info is ACTIVITIES, which belong to a chain of milestone time releases. Stages ...
0
votes
0answers
49 views

Options for implementing database and programming logic for statistics application?

I am implementing a statistics driven web application. The statistics relate to sporting events. Right now I have a postgresql database with a table that holds a row for each match. This is a ...
-1
votes
1answer
37 views

Querying Results from End of the table

Normal behavior of the SQL Table is to add new rows in end of the table. I want to query results of single row only. So mysql command is SELECT id FROM users WHERE country='india' LIMIT 1 this ...
0
votes
0answers
10 views

When would I choose a Lucene/MSFT Search of SQL to store Spatial data?

I need to store the locations of stores and allow customers to query nearby businesses, with a varying amount of filters. The filters in the Search world seem to be named "facets" What reasoning ...
3
votes
0answers
25 views

Using mysql 5.7 Json Columns for EAV

I am developing an e-commerce product and I have been able to implement all functionality and am left with allowing users to create additional attributes for a product. Right Now I have two options. ...
0
votes
0answers
96 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
29 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. ...
4
votes
2answers
1k 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 ...
-1
votes
0answers
19 views

Store SQL queries in the database or in the server script? [duplicate]

I used to do queries from PHP by manually making a query string like "SELECT * FROM ... WHERE ... AND ...", but my office does it by making stored procedures in the database and just querying "CALL ...
0
votes
1answer
71 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 ...
6
votes
5answers
8k views

Why SQL numeric precision is specified as the number of digits?

I understand that this might be a loaded question. For example, in Sql Server 2008, doing DECLARE @someNum NUMERIC(10); means that the range of the variable will be limited by 10 digits. In other ...
0
votes
0answers
19 views

Better way to build SQL query from form input? [migrated]

Very new to php. Using XAMPP, netbeans. I am using a <form> to send various input parameters as a GET to retrieve data from a very simple two table MySQL db. After filtering the inputs ...
0
votes
1answer
60 views

Designing Pricing table in MYSQL

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 ...
40
votes
8answers
5k views

Isn't the use of NoSQL Databases impractical in a large number of use cases? [closed]

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 ...
2
votes
0answers
81 views

Why isn't LIMIT [offset,]row_count in standard SQL? [closed]

SQL:2008 added support for a comparable OFFSET syntax OFFSET num {ROW|ROWS} FETCH {FIRST|NEXT} [num] {ROW|ROWS} ONLY. On the other hand, MySQL (and SQLite) support LIMIT offset, row_count or LIMIT ...
9
votes
5answers
7k views

Foreign key restrictions -> yes or no? [closed]

I would like to hear some "real life experience" suggestions if foreign key restrictions are good or bad thing to enforce in DB. I would kindly ask students/beginners to refrain from jumping and ...
7
votes
2answers
2k views

In what order are rows fetched absent ORDER BY clause?

One programmer is testing and comparing the same application which uses the same database structure, and the same data, only in two separate databases, one with Oracle 8 and one with Oracle 9. The ...
24
votes
6answers
50k views

SQL Triggers and when or when not to use them.

When I was originally learning about SQL I was always told, only use triggers if you really need to and opt to use stored procedures instead if possible. Now unfortunately at the time (a good few ...
4
votes
2answers
86 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
95 views

Relational table design for data ownership

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

Serialized values or separate table, which is more efficient?

I have a Rails model email_condition_string with a word column in it. Now I have another model called request_creation_email_config with the following columns admin_filter_group:references ...
1
vote
1answer
80 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 ...
12
votes
2answers
3k views

Is inline SQL still classed as bad practice now that we have Micro ORMs?

This is a bit of an open ended question but I wanted some opinions, as I grew up in a world where inline SQL scripts were the norm, then we were all made very aware of SQL injection based issues, and ...
10
votes
3answers
2k views

Storing n-gram data

I was hoping to brainstorm a little bit on the subject of storing n-gram data. In my project, I am trying to solve linguistic problems where I know all (n-1) data items and want to statistically guess ...
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 ...
22
votes
4answers
22k 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
780 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
18k 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 ...
5
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
68 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
510 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
45 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
72 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
233 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
34 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
163 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
116 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 ...
33
votes
6answers
61k 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
237 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) ...