If your data change after the application is deployed, how do you keep the database up to date? I mean, you can add or remove table, thats a simple task. Altering an existing table can be trivial too. But if you change the structure often, how do you keep that under a control? I used to keep a table with a current database version in ...
Is there some way to get a value from the last inserted row? I am inserting a row where the PK will automatically increase, and I would like to get this PK. Only the PK is guaranteed to be unique in the table. I am using Java with a JDBC and PostgreSQL. ...
The question is if a database connection should be passed in by reference or by value? For me I'm specifically questioning a PHP to MySQL connection, but I think it applies to all databases. I have heard that in PHP when you pass a variable to a function or object, that it is copied in memory and therefore uses twice as much memory imm...
What is the best way to manage a database connection in a Java servlet? Currently, I simply open a connection in the init() function, and then close it in destroy(). However, I am concerned that "permanently" holding onto a database connection could be a bad thing. Is this the correct way to handle this? If not, what are some bette...
I have a few questions about whether or not it would be best to not use indexing. BACKGROUND: My records have a timestamp attribute, and the records will be inserted in order of their timestamps (i.e., inserted chronologically). QUESTIONS: If I DON'T use indexing is it typical for the database to insert the records in the order that ...
In a database prototype, I have a set of fields (like name, description, status) that are required in multiple, functionally different tables. These fields always have the same end user functionality for labeling, display, search, filtering etc. They are not part of a foreign key constraint. How should this be modeled? I can think of t...
Hi all, I need to choose a database management system (DBMS) that uses the least amount of main memory since we are severely constrained. Since a DBMS will use more and more memory to hold the index in main memory, how exactly do I tell which DBMS has the smallest memory footprint? Right now I just have a memory monitor program open...
Hi, What percentage of your total development effort do you spend on implementing and maintaining simple Create, Read, Update and Delete (CRUD) methods in your data access layer? Does moving to an ORM like Hibernate or Entity Framework lead to significant savings? Are there any design smells which let you know when moving to an ORM for...
I have a set of tables that are used to track bills. These tables are loaded from an SSIS process that runs weekly. I am in the process of creating a second set of tables to track adjustments to the bills that are made via the web. Some of our clients hand key their bills and all of those entries need to be backed up on a more regular s...
I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists. It seems to be caused by import/restores not maintaining the sequence properly. ...
I have a certain POJO which needs to be persisted on a database, current design specifies its field as a single string column, and adding additional fields to the table is not an option. Meaning, the objects need to be serialized in some way. So just for the basic implementation I went and designed my own serialized form of the object w...
I'm planning to make a very simple program using php and mySQL. The main page will take information and make a new row in the database with that information. However, I need a number to put in for the primary key. Unfortunately, I have no idea about the normal way to determine what umber to use. Preferably, if I delete a row, that row's ...
I'd like to create a table which has an integer primary key limited between 000 and 999. Is there any way to enforce this 3 digit limit within the sql? I'm using sqlite3. Thanks. ...
We have a common problem of moving our development SQL 2005 database onto shared web servers at website hosting companies. Ideally we would like a system that transfers the database structure and data as an exact replica. This would be commonly achieved by restoring a backup. But because they are shared SQL servers, we cannot restore b...
In DB2, you can name a column ORDER and write SQL like SELECT ORDER FROM tblWHATEVER ORDER BY ORDER without even needing to put any special characters around the column name. This is causing me pain that I won't get into, but my question is: why do databases allow the use of SQL keywords for object names? Surely it would make more ...
For sure stackoverflow is gaining loads of momentum! Do you chaps want to share your other favorites? I understand there are different natures of DB forums though. To name a few: vendor specific programming specific administration specific business nature specific and so on... would appreciate if you could mention what verticals you...
What effect does a field name length has on the performance of a database? Is it negligible? Should I go with long descriptive names? ...
Hi, I am implementing replication for a project I am developing, and would like to replicate changes in the Write database to the Read database. While this isn't a problem, I want to tune one database for reading from, and the other to writing to, so they would have different settings. Is there any resource/guide which will tell me wh...
For example to create a hierarchy of categories you use a column 'parent_id', which points to another category in the same table. Should this be a foreign key? What would the dis/advantages be? ...
We may tag a question with multiple tags in StackOverflow website, I'm wondering how to find out the most related questions with common tags. Assume we have 100 questions in a database, each question has several tags. Let's say user is browsing a specific question, and we want to make the system to display the related questions on the p...