database

Database Version Control

Is there a good way to check database structure into a SVN? Perhaps checking in a DDL file when changed? Any ideas?...

Locking a SQL Server Database with PHP

I'm wanting extra security for a particular point in my web app. So I want to lock the database (SQL Server 2005). Any suggestions or is this even necessary with SQL Server? Edit on question: The query is failing silently with no errors messages logged, and does not occur inside of a transaction. Final Solution: I never was able to s...

Bigger than a char but smaller than a blob

Char's are great because they are fixed size and thus make for a faster table. They are however limited to 255 characters. I want to hold 500 characters but a blob is variable length and that's not what I want. Is there some way to have a fixed length field of 500 characters in MySQL or am I going to have to use 2 char fields?...

How do you persist a tree structure to a database table with auto incrementing IDs using an ADO.NET DataSet and a DataAdapter

I have a self-referential Role table that represents a tree structure ID [INT] AUTO INCREMENT Name [VARCHAR] ParentID [INT] I am using an ADO.NET DataTable and DataAdapter to load and save values to this table. This works if I only create children of existing rows. If I make a child row, then make a child of that child, then Update, ...

Inheritance in database?

Is there any way to use inheritance in database (Specifically in SQL Server 2005)? Suppose I have few field like CreatedOn, CreatedBy which I want to add on all of my entities. I looking for an alternative way instead of adding these fields to every table....

Issues using MS Access as a front-end to a MySQL database back-end?

Two users wanted to share the same database, originally written in MS Access, without conflicting with one another over a single MDB file. I moved the tables from a simple MS Access database to MySQL using its Migration Toolkit (which works well, by the way) and set up Access to link to those tables via ODBC. So far, I've run into the ...

What's your opinion on using UUIDs as database row identifiers, particularly in web apps?

I've always preferred to use long integers as primary keys in databases, for simplicity and (assumed) speed. But when using a REST or Rails-like URL scheme for object instances, I'd then end up with URLs like this: http://example.com/user/783 And then the assumption is that there are also users with IDs of 782, 781, ..., 2, and 1. Assu...

Normalizing a Table with Low Integrity

I've been handed a table with about 18000 rows. Each record describes one customer location. The issue is, that when the person created the table, they did not add a field for "Company Name", only "Location Name," and one company can have many locations. For example, here are some records that describe the same customer: Location Table...

Is there a way to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?

To illustrate, assume that I have two tables as follows: VehicleID Name 1 Chuck 2 Larry LocationID VehicleID City 1 1 New York 2 1 Seattle 3 1 Vancouver 4 2 Los Angeles 5 2 Houston I want to write a query to return the following resul...

What's the best way of converting a mysql database to a sqlite one?

I currently have a relatively small (4 or 5 tables, 5000 rows) MySQL database that I would like to convert to an sqlite database. As I'd potentially have to do this more than once, I'd be grateful if anyone could recommend any useful tools, or at least any easily-replicated method. (I have complete admin access to the database/machines...

Does running a SQL Server 2005 database in compatibility level 80 have a negative impact on performance?

Our software must be able to run on SQL Server 2000 and 2005. To simplify development, we're running our SQL Server 2005 databases in compatibility level 80. However, database performance seems slower on SQL 2005 than on SQL 2000 in some cases (we have not confirmed this using benchmarks yet). Would upgrading the compatibility level t...

Dropping a group of tables in SQL Server

Is there a simple way to drop a group of interrelated tables in SQL Server? Ideally I'd like to avoid having to worry about what order they're being dropped in since I know the entire group will be gone by the end of the process. ...

Querying like Linq when you don't have Linq.

I have a project that I'm currently working on but it currently only supports the .net framework 2.0. I love linq, but because of the framework version I can't use it. What I want isn't so much the ORM side of things, but the "queryability" (is that even a word?) of Linq. So far the closest is llblgen but if there was something even li...

Database, Table and Column Naming Conventions?

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions: Should table names be plural? Should column names be singular? Should I prefix tables or columns? Should I use any case in naming items? Are there any recommended guidelines out ther...

Recommendation for a PostgreSQL Programming Tutorial?

Can anyone suggest a good guide/tutorial for programming PostgreSQL? By this I mean creating triggers and procedures, domains, new types, using the various languages, and so on. The manual is good, but I found it lacking as a learning aid (it is more of a reference, as manuals should be)? It is very good at describing the SQL syntax and...

What's your favourite way of interacting with DBs from your programming language?

There are numerous ways to connect and interact with the DB layer. In Java, for example, common usages are JDBC calls of raw SQL, object relational mappers, JDBCTemplate (Spring), stored procedures, etc. In your language, which option is your preference and why? When would you consider the others? ...

any sample MySQL databases I can download?

I'm doing some inter-database operational research... e.g. synchronizing Oracle, MySQL, etc. Are there any nice MySQL databases that I can download, so that I can test some importing on real-world cases? I'm thinking of some open project that might have a weekly data dump available for download. Of course, anything similar for Oracle,...

Instrumenting Database Access

Jeff mentioned in one of the podcasts that one of the things he always does is put in instrumentation for database calls, so that he can tell what queries are causing slowness etc. This is something I've measured in the past using SQL Profiler, but I'm interested in what strategies other people have used to include this as part of the a...

Anyone know of an on-line free database?

I wrote an application that currently runs against a local instance of MySql. I would like to centralize the DB somewhere on the Net, and share my application. But, I'm cheap, and don't want to pay for it. Does anyone know of a free on-line relational DB service that I could connect to via C#? ...

Use for the phppgadmin Reports Database?

phppgadmin comes with instructions for creating a reports database on the system for use with phppgadmin. The instructions describe how to set it up, but do not really give any indication of what its purpose is, and the phppgadmin site was not very helpful either. It seems to allow you to store SQL queries, so is it for storing admin qu...