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
278 views

Formatting SQL statements [duplicate]

In recent years I've developed a habit of formatting SQL SELECT queries like so: SELECT fieldNames FROM sources JOIN tableSource ON col1 = col2 JOIN ( SELECT ...
2
votes
1answer
149 views

Testing MVC models when using SQL referential constraints

I have an MVC web app that's wired against PostgreSQL. The DB uses the full gamut of various referential constraints to ensure referential integrity, as opposed to say the Rails approach, where you ...
18
votes
9answers
3k views

Should a primary key be immutable?

A recent question on stackoverflow provoked a discussion about the immutability of primary keys. I had thought that it was a kind of rule that primary keys should be immutable. If there is a chance ...
6
votes
1answer
1k views

Is SQL declarative?

I ask because so many of the questions I see in SQL amount to: "This is slow. How do I speed it up"? Or are tutorials stating "Do this this way and not that way as it's faster". It seems to me that a ...
1
vote
1answer
71 views

SQL Table Design Modification based on data roles or implementation convenience?

I've got an existing SQL table structure that looks like this: Orders - order_id - organisation_id - [all the rest of the order data] Organisations - organisation_id - [all the rest of the ...
9
votes
3answers
2k views

Storing n-gram data

(hopefully I landed on the right stack exchange site) 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 ...
1
vote
1answer
264 views

How can I maintain a SQL schema upgrade that goes out to our users on a regular basis?

I work on a project that uses a SQL database as a back end, and a desktop app as a front end. We do releases on a regular basis, and as part of a release, our client needs to update their SQL server. ...
3
votes
2answers
1k views

Saving user profiles in SQL database

So, I got a table UserProfiles in my database, that, similar to C#'s Settings file, holds a property with the appropriate datatype and the specified setting in the profile. Now each time I add a new ...
4
votes
1answer
464 views

Designing a universal database driver class

I have a homebrew MVC framework that I have been using for a while, and it has been working great, but I am planning an upgrade to it for its ORM classes. So far, it only supports SQL databases by ...
1
vote
3answers
462 views

Protect Database Design - SQL

Perhaps a bit of an odd question but I can't think of a way to achieve what I want. I produce a product which ends up installed on-site at clients premises. It is a business critical and high ...
0
votes
4answers
234 views

Create a database for database tables

I'm working on a project where I have to give users the ability to build their own tables, each user can create more than one table. (the type of all data is string). The user can create a table and ...
0
votes
1answer
431 views

What is the proper and easy way to move a LocalDB to Azure using code first ?

The only way I could figure how to do it was create the schema on the localDB with code first and then manually change the database string to Azure and then run the code on Azure.. But that only ...
0
votes
0answers
57 views

Document existing Visual Studio Project [duplicate]

I am a small business owner and my sole developer quit. Cold. I am not a coder. He developed an existing application that works .net, c#, javascript, sql, visual studio based, 250,000 lines of code. ...
3
votes
6answers
1k views

OOP (php) for beginners - some unclarities

I started reading some tutorials about OOP, because I want to learn the basics. I have a question about it. I understand how you can have a object "Car" and give it a color like this ...
0
votes
2answers
209 views

Should I retrieve one large set of data to filter or smaller individual sets of data?

I have a company intranet that utilizes a similar set of data across many different pages and many different methods. Speaking in generalities, is it closer to "best practice" to retrieve a large ...
1
vote
2answers
688 views

Registration: Email hash and verifying only one account per email

I'm using a registration function that hashes the email in PBKDF2 with a random and unique Salt each time. The hashes email and his salt are saved in the DB. No problem with that. The problem is that ...
0
votes
1answer
3k views

Best place to write SQL queries

I've been working on this project for my company. Currently I am embedding my SQL statements inside the program itself as and when they are needed. I do have two seperate classes - QueryBuilder ...
1
vote
1answer
125 views

Visual Studio DBProjects and problem with it running post-build SQL (file too big)

What I am doing is creating a DBProject.. importing a DB.. then creating scripts in the PostBuild that are DATA scripts for each table (so that the post build can populate the tables after the schema ...
2
votes
2answers
357 views

Best strategy in SQL

Recently, a colleague told me that it wasn't advisable to make conditions in the join clauses. Instead he suggested to make conditions in the where clause. He told me that the SQL engine was optimized ...
0
votes
2answers
225 views

ACID compliant Database that isn't NoSQL? [closed]

I'm not necessarily asking if a NoSQL database can be ACID compliant, which has been asked here: Is there any NoSQL that is ACID compliant? I'm wondering if we have a database either now or in the ...
1
vote
2answers
221 views

Are error codes wrong in SQL queries too?

I have an SQL query which does several things at once¹: it does multiple checks and then, if the checks pass, updates some data. Without entering in the domain-specific details, here's an imaginary ...
3
votes
2answers
211 views

Referencing countries via Foreign Key Constraint

To make things simple to explain: I've got two tables: Table1 and Countries. The Countries table contains a nice flag and some information, such as DisplayName, the ISO3166 ALPHA3 code and the phone ...
2
votes
2answers
422 views

Not using SQL Transactions

I am looking at a codebase where a developer never uses SQL transactions i.e. each update/insert into the SQL database is an atomic operation. I believe the codebase would benefit from transactions. ...
0
votes
1answer
854 views

TFS SQL Deployment Data Script

We are using TFS and SQL Server 2005 (looking to upgrade to SQL Server 2012 if that makes a difference). We store our database schema in a Visual Studio Database project (VS 2010). When code is ...
9
votes
4answers
1k views

Will working in IT limit your career prospects as a programmer? [duplicate]

I took a job working as an IT guy (SQL programming, helpdesk, etc.) because I had need of a job (to pay back student loans accumulated from school). I'm very happy to have a job, but I eventually ...
11
votes
4answers
13k views

why are noSQL databases more scalable than SQL? [duplicate]

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 ...
0
votes
3answers
182 views

How to maintain user relationship when user unfollows another user

I have a table of user relationship which stores who follows whom, the table structure is as follows -id -follower -following when someone follows any other user, I add a record with follower user ...
5
votes
1answer
662 views

OpenID implementation - PHP, Javascript, MySQL

I've started doing some research on the technologies that I will need for my website. I'm trying to implement a really simple website with OpenID user registration. The website will store a block of ...
9
votes
4answers
1k views

Why do relational databases only accept SQL queries?

As far as I know, most relational databases do not offer any driver-level API for queries, except a query function which takes an SQL string as an argument. I'm thinking how easier it would be if one ...
3
votes
1answer
1k views

Calling MSSQL stored procedure from Zend Controller ? Any other approaches?

MSSQL and DB, Zend as PHP Framework, I am using this way to call SP with I/P Parameters and to get O/p Parameters. It seems I am writing SQL code in PHP. Any other good approaches? $str1 = ...
1
vote
2answers
471 views

Why OTServs have an item cloning problem if the server crashes? [closed]

OTServs are open source MMORPGs with a huge community. Mostly all of them have a serious problem: if the server crashes, people can clone items. This is a dirty trick that can be executed because the ...
12
votes
6answers
2k views

Do You Still Need Indexing After Database Normalization

After you have done a good normalization, do you still need to index the table? How is this going to affect the performance? Will it even affect the performance in some way after a good normalization? ...
1
vote
2answers
171 views

Is NoSQL a good fit for a graphing app, and how can I compare different NoSql Servers?

I am traditionally a MsSQL or MySQL developer - I have been prototyping a new application that involves storing data from many targets. I have seen that the app appears to get slow after scaling to ...
1
vote
1answer
361 views

Database data structures for RESTful api

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
1
vote
1answer
528 views

Established coding standards for pl/pgsql code

I need to standardize coding practices for project that compromises, among others, of pl/pgsql database, that has some amount of nontrivial code. I look for: Code formatting guidelines, ...
6
votes
3answers
2k views

Managing SQL Stored Procedures' Version

I am working with a team of 5 persons. We are using SQL Server as our database. Since long time I want to store the Stored Procedures in SVN so that the versions can be maintained. Is there any tool ...
3
votes
2answers
2k views

Why is NoSQL better for this scenario?

Hypothetical scenario: Let's say we are downloading JSON from Facebook with details of a user's friend's checkins, posts, etc... These come in as one document per friend per activity, so with 8 ...
2
votes
1answer
717 views

Internationalization through SQL Database and performance issues

I'm using .Net technologies and because ASP.Net "instant translation" is not really easy to do with ResX, since it has to be compiled after every change, there are a few hacks available ...
4
votes
2answers
754 views

Formatting SQL Based on an AST

I am working on my .NET open source project for generating SQL... Initially, I wrote the project so it spit out exactly one SQL format... ugly. I started using this project in the real world and ...
6
votes
4answers
556 views

Good idea to move logic out of SQL statements?

I'll preface this question by saying that I am very new to professional software dev. I work on a team that takes data in from other groups in my company and turns this data into reports usable by ...
3
votes
4answers
1k views

What has been your experience with SQL CLR for complex business logic?

So I thought I had a perfect use-case for a CLR SQL Procedure. I've search the Net for perhaps a similar example where data is retrieved, records added and updated. I have not looked at a SQL CLR ...
4
votes
2answers
190 views

How would I make a suggestion for a change to the SQL standard

If I wanted to make a suggestion to a change to how the UPDATE statement works in SQL, how would I go about it? Is there a website for the next standard? I googled, but just kept getting the ...
2
votes
1answer
694 views

Is Clojure a 3GL or a 4GL?

A bit of background (in case I'm mistaken)... I think I understand that (it's an oversimplification): manually entering codes into memory (or on a punchcard) is "first generation language" using ...
4
votes
1answer
398 views

Algorithm to denormalize database

I have built a QlikView script generator (QVS), that can be used to generate large script files for data imports from SQL Server to QlikView. QlikView does not like 'fully' normalised database ...
1
vote
1answer
245 views

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

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 ...
9
votes
4answers
7k 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 ...
0
votes
1answer
354 views

For PL/SQL, do large companies prefer ANSI SQL joins or old Oracle joins? [closed]

I am interviewing for a PL/SQL position with a large corporation. I will have to write a multiple choice exam. I am wondering whether the exam will likely use old-style joins (joining happens in the ...
2
votes
2answers
2k views

SQL Auto Formatting and Auto Capatilize in Vim

I'd like to use vim for writing SQL queries, but I haven't figured out yet how to get two key features that I use all the time in programs like SQL Yog. I usually just start up an instance of Yog in ...
1
vote
1answer
779 views

Static Tables vs Dynamic Entity Attributes Storage Configurations

I am trying to figure out a better way to decide table storage configurations whether it will use static configurations using tables or dynamic configurations using EAV (entity-attribute-value) When ...
4
votes
2answers
633 views

Best practice to represent SQL in program source code

Where SQL statements are executed from a program, what is the best practice to store the statement text? Most often it is in a read only string, in the source file it is executed in. I think a ...