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.
6
votes
3answers
139 views
Encapsulation for complex queries
I tried asking this question first on StakOverflow in a more concrete manner, but after being pointed here I realized I should rephrase it in more general terms; however, you can still review the ...
-1
votes
1answer
68 views
Registration form with email verification
Can someone point me in the right direction: I need to make a website with a registration form, to create accounts with e-mail verification, to write the forms content in a database table or something ...
1
vote
0answers
24 views
ODBC 3 Multiple Statements vs Multiple Connections
So right now I have a single thread to handle all the requests for the database. Let's say I have 400 requests per second for logins / logouts / other stuff, and 400 requests per second which are only ...
0
votes
1answer
59 views
Why storing a payroll in XML type field would be good or bad
We have a PAYROLL issues by the client to make it dynamic i.e. to make the whole process computerized.
It includes:
Employees
Allowances (Scale wise, which could change yearly or monthly or ...
-4
votes
0answers
45 views
Move data from src db to target db table using xml mapper [closed]
Suppose we have two xml files, one have define source connection string, target connection string and other one xml having define mapping between srcdb and trgt db.
I have to read source connection ...
0
votes
3answers
105 views
Getting RowCount without wasting resources
I have a large SQL query that relates 6-7 different tables and returns fourteen different rows as distinct sets, in a union with an equally-large query, that at times can return over a thousand ...
1
vote
0answers
53 views
SQL Query and Java Constant Abuse?
I am currently charged with taking over a lot of code that is written with fields names placed into Java constant at the top of the file, and then the SQL queries constructed using string ...
1
vote
0answers
33 views
auto generated web CMS for pre-existing SQL db?
What I'm looking for is a way to auto-generate a simple web-based CMS for a simple pre-existing SQL database. To be used by 'app administrators', not the general public.
Something that:
allows ...
1
vote
0answers
36 views
More scalable alternative to a transaction-per.request in a SQL+REST stack?
I'm new in web programming with SQL database. So please forgive my ignorance.
I'm using some modern framework to make a REST server with a SQL database. I'm using transaction only when I think I need ...
-2
votes
0answers
25 views
Designing the database about web application [duplicate]
How should I declare the images in SQL Server database. Should I create a seperate table for images or should i include the image attribute in tables where is needed.
2
votes
2answers
203 views
SQL query or C# .net code for csv files import?
My aim here is to find out the best possible and feasible solution for my dilemma. I want to import some csv file (may contain around 50~60K records) into database after some manipulation into the ...
0
votes
0answers
14 views
Conceptually, how should a unidirectional multivalued relationship be implemented?
Ok, I'm trying not to upset the programmers list, but not so successful so far. My apologies. I'm trying to understand, conceptually and based on experience, which is the correct way for a JPA ...
4
votes
2answers
100 views
Why are triggers seen as a last resort to solve multiple paths in cascading deletes?
There is a problem using ON DELETE CASCADE on foreign keys in a SQL database if there are multiple paths from the root foreign key to the leaf. The way around this seems to be to replace the ON DELETE ...
2
votes
2answers
115 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, ...
0
votes
1answer
101 views
Relationship between REST APIs and Databases [duplicate]
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 ...
0
votes
0answers
47 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
83 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:
...
0
votes
0answers
14 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
62 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
44 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
54 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
99 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
37 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
92 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 ...
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
88 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
102 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
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 ...
1
vote
1answer
84 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
51 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
73 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
241 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 ...
0
votes
1answer
35 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
124 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 ...
3
votes
2answers
286 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
122 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
181 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
158 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
319 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
151 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
446 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
204 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
89 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
110 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
85 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
115 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
671 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 ...
