Currently I have a custom tool which generates vanilla-SQL (only using standard SQL), from our Data-models that sets up database schemas and initial data for our new databases, and do version patches, etc... This part is all fine and dandy right now. However running these SQL scripts against all the different databases (different vendor...
My boss asks me to write only ANSI SQL to make it database independent. But I learned that it is not that easy as no database fully ANSI SQL compatible. SQL code can rarely be ported between database systems without modifications. I saw people do different way to make their program database independent. For example: Externalize SQL st...
Why does the SQL Standard accept this? Which are the benefits? If have those tables: create table prova_a (a number, b number); alter table prova_a add primary key (a,b); create table prova_b (a number, b number); alter table prova_b add foreign key (a,b) references prova_a(a,b) ; insert into prova_a values (1,2); You can insert thi...
This question is about database views, not materialized-views. Pros: Query simplification. Avoid repeat the same joins on multiples queries. Avoid magic numbers. Cons: Hiding real queries (may be you are repeating joins). What else? ...
What is the best database schema to represent an NCAA mens basketball bracket? Here is a link if you aren't familiar: http://www.cbssports.com/collegebasketball/mayhem/brackets/viewable_men I can see several different ways you could model this data, with a single table, many tables, hard-coded columns, somewhat dynamic ways, etc. You n...
I looking for the best way to check for inter-table constraints an step forward of foreing keys. For instance, to check if a date child record value is between a range date on two parent rows columns. For instance: Parent table ID DATE_MIN DATE_MAX ----- ---------- ---------- 1 01/01/2009 01/03/2009 ... Child table PARENT_ID...
Like an idiot, I completely overlooked the timezone setting when I first built an application that collects datetime data. It wasn't an issue then because all I was doing was "time-since" style comparisons and ordering. Now I need to do full reports that show the actual datetime and of course, they're all stored at America/Chicago (the ...
We want to be able to select top N rows using a SQL Query. The target database could be Oracle or MySQL. Is there an elegant approach to this? (Needless to say, we're dealing with sorted data here.) ...
This is a simplified version of a query we are running where we need to find all rows in the main parent table where the child rows match. The query below returns no results when one of the child tables is empty. The main table has two child tables: CREATE TABLE main (id INT PRIMARY KEY, name VARCHAR(8)); CREATE TABLE child1(id INT P...
I have a cms which stores comments against articles. These comments can be both threaded and non threaded. Although technically they are the same just with the reply column left blank when it's not threaded. My application works on sqlLite, MySQL and pgsql so I need fairly standard SQL. I currently have a comment table comment_id artic...
If I need to search on Date of birth which is stored without the hours and minutes but the date I have to search with includes hours and minutes what is the best way to return all rows where date is matched on only day, month and year i.e. Stored as 01-JAN-50 10.22.06.000000000 date selected 01-JAN-50 10.22.06.010101120 If I us...
Hi there, I was wondering, is there a way to create a timestamp in c# from a datetime? I need a millisecond precision value that also works in Compact Framework(saying that since DateTime.ToBinary() does not exist in CF). My problem is that i want to store this value in a database agnostic way so i can sortby it later and find out whic...
I have these two lines in my model, written for PostgreSQL: named_scope :by_month, lambda { |month| { :conditions => ["EXTRACT(MONTH FROM recorded_on) = ?", month] }} named_scope :by_year, lambda { |year| { :conditions => ["EXTRACT(YEAR FROM recorded_on) = ?", year] }} I'm running PostgreSQL in production, but I'm developing with SQLi...
I realize this question is very likely to have been asked before, but I've searched around a little among questions on StackOverflow, and I didn't really find an answer to mine, so here goes. If you find a duplicate, please link to it. For some reason I prefer to use Guids (uniqueidentifier in MsSql) for my primary key fields, but I rea...
Hi All, Is anyone aware of a vendor neutral XSD to describe a relational database schema? Our system needs to grab information about the structure of a database: Tables Columns and types Primary and Foreign Keys Constraints Indexes etc in a vendor independent manner and store it in an XML file for later processing. Before we...
As a junior programmer I think it may be beneficial to learn solid database design. What books would you recommend on learning database design? Just to be clear, the specific aspect of design I am referring to is creating a collection of tables and their relationships. Note: The programming books I have enjoyed reading (gaged by teachin...
In reading this and this and then reading this (which references the other two ironically) I find myself wondering just how big the discussion of this topic is? I am a SQL Server guy and so I tend to use an Identity that is auto generated in the form of an int. However, when I know that I will need some form of replication between serv...
What is database clustering? If you allow the same database to be on 2 different servers how do they keep the data between synchronized. And how does this differ from load balancing from a database server perspective? ...
I'm looking for Static Analysis Tools for Database Tier. I got some answers for reviewing PLSQL, TSQL code, i'm wondering what are the options available for reviewing database design for naming conventions of tables and their columns, foreign key constraints and triggers etc. There is MSDN article which talks about ApexSQL Enforce, but ...
Hello, I am currently designing a .NET application using NHibernate as its DAL. I want the application to be as DB agnostic as possible and NHibernate certainly helps me achieve this. However, I know my application will need to have reporting capabilities and was wondering if there is an existing framework or set of best practices for cr...