database

when will oracle discontinue the 9i database?

The Oracle 9i database is starting to look long in the tooth. When will Oracle discontinue support for it? ...

Separate production database for logging

I've got an application in test that's logging events for a departmental Windows forms application to a SQL Server database. The application is nearly ready for production. The logging database is completely separate from the application database. My question is, do I really need to create a production version of the logging database, ...

Effecient way to model aggregate data of a many-to-one relationship (e.g. votes count on a stackoverflow question)

I'm curious about what be the best way to model this for optimized performance... not as concerned about real time data integrity I'll continue with the stackoverflow example Question id title Votes id user question A question has many votes For many queries however, we're only concerned with the aggregate number of votes ...

How do I avoid the use of getdate() in a SQL view?

I'm writing a database view to sum up a bunch of records where the value in a date column is within the last 7 days. It looks something like this: CREATE VIEW RecentRecordSum AS SELECT t.ID, SUM(t.SomeValue) AS ValueSum FROM SomeTable t WHERE t.RecordDate >= DATEADD(d,-7,GETDATE()) GROUP BY t.ID Is ...

How would you design your database to allow user-defined schema

If you have to create an application like - let's say a blog application, creating the database schema is relatively simple. You have to create some tables, tblPosts, tblAttachments, tblCommets, tblBlaBla… and that's it (ok, i know, that's a bit simplified but you understand what i mean). What if you have an application where you want ...

Retrieving and saving database fields by ID

Data in one table is referenced by ID to another Table. For example, I have a table of States with ids 1-50. In another table "User" I have a reference to that id ie. state= 4. When I need to update data back to "User", if the state changes, should my code be aware of the numbering of the state data? In other words, if the new state i...

Why my search wont refine!?!?!

I have this bit of code: //Restrict the SQL query with an AND clause if a member has been selected if ($form_member_id != 0) { $query .= "AND photos.member_id = '$form_member_id' "; } It is meant to refine a search query down to only the selected user, so the whole query together reads: SELECT photos.photo_id, members.member_name, p...

How would you build a database filesystem (DBFS)?

A database file system is a file system that is a database instead of a hierarchy. Not too complex an idea initially but I thought I'd ask if anyone has thought about how they might do something like this? What are the issues that a simple plan is likely to miss? My first guess at an implementation would be something like a filesystem...

Reconstruction User based Modular Website

I am developing a website in Ruby on Rails, MySQL, and Javascript. The website is modular, meaning that a user can customize their homepage and drag and drop one module to another area where it will live. there are three columns where the module can be dropped. (Think iGoogle or Netvibes) What is the best way to store this data, so th...

SSIS Union All problem - is there a bug in SSIS for this item?

I am using SQL 2005 and SSIS.............. I have 2 data sources. One from table A and one from table B. I want to move data from table A to table B. But first i get the MAX date from both and compare them. If they are the same then i must either stop the SSIS package or use the Conditional Split. But when the MAX date from table B g...

Using views in a datawarehouse

I recently inherited a warehouse which uses views to summarise data, my question is this: Are views good practise, or the best approach? I was intending to use cubes to aggregate multi dimensional queries. Sorry if this is asking a basic question, I'm not experienced with warehouse and analyis services Thanks ...

Any example of a necessary nullable foreign key?

Customers customer_id Orders order_id customer_id fk If I have two tables and define a foreign key on customer_id in the Orders table, by allowing it to be null I am saying that I can have an order that does not have a customer associated with it. As such, the notion of a nullable foreign key seems at odds with the purpose of a for...

Database Design and the use of non-numeric Primary Keys

Hello, I'm currently in the process of designing the database tables for a customer & website management application. My question is in regards to the use of primary keys as functional parts of a table (and not assigning "ID" numbers to every table just because). For example, here are four related tables from the database so far, one ...

How do I create nested categories in a Database?

I am making a videos website where categories will be nested: e.g. Programming-> C Language - > MIT Videos -> Video 1 Programming -> C Language -> Stanford Video - > Video 1 Programming -> Python -> Video 1 These categories and sub-categories will be created by users on the fly. I will need to show them as people create them ...

Is there a way to view relationships in Oracle SQL Developer?

I want to find which tables are related to a specific table. I can see all the foreign key constraints easily enough, but what about table for which the table I am looking at is the primary key table and the other table is the referenced table. ...

PostgreSQL vs. mySQL

I tried to Google this but it seems like most of the answers are terribly outdated. So, the question is simple: why would I choose one over the other? What are the benefits of each, and what drawbacks do they have? EDIT: Please close this. Apologies, I didn't see the other duplicates when I did a search earlier. ...

Maintaining Customer Data

All our applications rely on a certain amount of client data i.e. Lookups for comboboxes, users, roles, user-roles... We currently use a spreadsheet to map the data and generate insert scripts that are then imported into SQL server. The has seemed to work for us but it is very difficult to update and maintain when there are a lot of ch...

How to only display a TreeView expand sign [+] if children exist

I've developed an application that populates a treeview from hierachical data in a database. I've designed it to use lazy-loading so it only gets the child nodes when a node is expanded. My problem is that obviously I don't know if a node has children unless I make a call to the database and look. Currently I have implemented a dummy c...

Oracle vs. Hypersonic SQL

I need to select by date in a SQL query, for example SELECT * FROM foo WHERE date = '2009-09-09' That query works in my Hypersonic test database, but not Oracle, which seems to requires: SELECT * FROM foo WHERE date = TO_DATE('2009-09-09', 'yyyy-mm-dd') Is there a way to select by date uniformly across these two databases? ...

Problem using two xa-datasources with Jbpm

Hello, I'm using Seam, JBoss Jbpm and Resteasy. I have a very simple process definition: <start-state name="create"> <transition name="send" to="inbox" /> </start-state> <node name="inbox"> <action expression="#{test.sayHello}" /> <transition name="acknowledge" to="acknowledged" /> </node> <end-state name="acknowledged" /> Thi...