How do I avoid read locks in my database?
Answers for multiple databases welcome! ...
database
find duplicate addresses in database, stop users entering them early?
How do I find duplicate addresses in a database, or better stop people already when filling in the form ? I guess the earlier the better? Is there any good way of abstracting street, postal code etc so that typos and simple attempts to get 2 registrations can be detected? like: Quellenstrasse 66/11 Quellenstr. 66a-11 I'm talking Ge...
Swapping column values in MySQL
I have a MySQL table with coordinates, the column names are X and Y. Now I want to swap the column values in this table, so that X becomes Y and Y becomes X. The most apparent solution would be renaming the columns, but I don't want to make structure changes since I don't necessarily have permissions to do that. Is this possible to do w...
Create DB in Sql Server based on Visio Data Model
I have created a database model in Visio Professional (2003). I know that the Enterprise version has the ability to create a DB in Sql Server based on the data in Visio. I do not have the option to install Enterprise. Aside from going through the entire thing one table and relationship at a time and creating the whole database from scrat...
Good reasons NOT to use a relational database?
Can you please point to alternative data storage tools and give good reasons to use them instead of good-old relational databases? Imo most applications rarely use full power of SQL, it would be interesting to see how to build a SQL-free application. ...
ActiveRecord
Does anyone have any recommendations for implementations of ActiveRecord in PHP? I've been using CBL ActiveRecord, but I was wondering if there were any viable alternatives. ...
howto track data changes in a database table
What is the best way to track changes in a database table? Imagine you got an application in which users (in the context of the application not DB users ) are able to change data which are store in some database table. What's the best way to track a history of all changes, so that you can show which user at what time change which data h...
Does Adobe Flash support databases?
Which databases does Adobe Flash support, if any? ...
What design pattern is PEAR DB_DataObject implementing?
DB_DataObject does not appear to be ActiveRecord because you do not necessarily store business logic in the "table" classes. It seems more like Table Data Gateway or Row Data Gateway, but I really cannot tell. What I need is good ORM layer that we can use with DataMapper and a DomainModel. Any ideas? ...
One or Two Primary Keys in Many-to-Many Table?
I have the following tables in my database that have a many-to-many relationship, which is expressed by a connecting table that has foreign keys to the primary keys of each of the main tables: Widget: WidgetID (PK), Title, Price User: UserID (PK), FirstName, LastName Assume that each User-Widget combination is unique. I can see two...
Enforce constraint checking only when inserting rows in MSSQL?
Is there a way to enforce constraint checking in MSSQL only when inserting new rows? I.e. allow the constraints to be violated when removing/updating rows? Update: I mean FK constraint. ...
"Similar Posts" like functionality using MS SQL Server?
I have lots of article store in MS SQL server 2005 database in a table called Articles- "Articles (ArticleID, ArticleTitle, ArticleContent)" Now I want some SP or SQL query which could return me similar Article against any user's input (very much like "Similar Posts" in blogs OR "Related Questions" in stackoverflow). The matching shou...
Database Design for Revisions?
We have a requirement in project to store all the revisions(Change History) for the entities in the database. Currently we have 2 designed proposals for this: e.g. for "Employee" Entity Design 1: // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds the Employee Revisions in Xml. The Re...
Database exception handling best practices
How do you handle database exceptions in your application? Are you trying to validate data prior passing it to DB or just relying on DB schema validation logic? Do you try to recover from some kind of DB errors (e.g. timeouts)? Here are some approaches: Validate data prior passing it to DB Left validation to DB and handle DB exception...
Developer moving from SQL Server to Oracle
We are bringing a new project in house and whereas previously all our work was on SQL Server the new product uses an oracle back end. Can anyone advise any crib sheets or such like that gives an SQL Server person like me a rundown of what the major differences are - Would like to be able to get up and running as soon as possible. ...
Languages other than SQL in postgres
I've been using PostgreSQL a little bit lately, and one of the things that I think is cool is that you can use languages other than SQL for scripting functions and whatnot. But when is this actually useful? For example, the documentation says that the main use for PL/Perl is that it's pretty good at text manipulation. But isn't that m...
How to download a live MySQL db into a local test db on demand, without SSH?
I have a fairly small MySQL database (a Textpattern install) on a server that I do not have SSH access to (I have FTP access only). I need to regularly download the live database to my local dev server on demand; i.e., I would like to either run a script and/or have a cron job running. What are some good ways of doing this? Some points ...
How do you build a multi-language web site?
A friend of mine is now building a web application with J2EE and Struts, and it's going to be prepared to display pages in several languages. I was told that the best way to support a multi-language site is to use a properties file where you store all the strings of your pages, something like: welcome.english = "Welcome!" welcome.spani...
Best way to do multi-row insert in Oracle?
I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle. INSERT INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, FROM_CURCY_CD, TO_CURCY_CD, EXCH_EFF_DATE, EXCH_EFF_END_DATE, EXCH_LAST_UPDATED_DATE) V...
Transactions best practices
How much do you rely on database transactions? Do you prefer small or large transaction scopes ? Do you prefer client side transaction handling (e.g. TransactionScope in .NET) over server side transactions or vice-versa? What about nested transactions? Do you have some tips&tricks related to transactions ? Any gotchas you...