database

Differentiating between "AB" and "Ab" in a character Database Field

Specifically, Sql Server 2005/T-Sql. I have a field that is mostly a series of two characters, and they're all supposed to be upper case but there's some legacy data that predates the current DB/System, and I need to figure out which records are in violation of the upper casing covenant. I thought this would work: select * from tbl wh...

How can I get my database under version control with Perl?

I've been looking at the options for getting our database schemas under version control. It seems that Ruby folks have got Rails Migrations, and .NET folks have got a few options (for instance this, this, and this). What about Perl? I've seen this thread on PerlMonks which doesn't have much, although it mentions DBIX::Migration::Direc...

Defining Multiple Foreign Keys in Visual Studio

I have a basic database schema as follows (dumbed down so it's easy to read): [Staff] StaffId (pk) FirstName LastName [RosterEvent] EventId (pk) StartDate EndDate [StaffEvents] StaffId (pk) EventId (pk) Ok so, many Staff can have many RosterEvents, which is why I added the StaffEvents table. It resolves the many-to-many relationship...

Recommended location for document storage - in database or elsewhere?

Background: We have an in house document storage system that was implemented long ago. For whatever reason, using the database as the storage mechanism for the documents was chosen. My question is this: What is the best practice for storing documents? What are the alternatives? What are the pros and cons? Answers do not have to...

Database as fileformat

A niave beginners question about database design. I have an app managing some logger data eg. 1000s of sequential measurements of time, voltage, current, temperature. In addition each sequence run has meta data (date, location, etc). So I need a table for each set of measurements and a master table listing these tables and the meta data ...

Best performing way to guarantee data consistency between concurrent web service calls?

Multiple clients are concurrently accessing a JAX-JWS webservice running on Glassfish or some other application server. Persistence is provided by something like Hibernate or OpenJPA. Database is Microsoft SQL Server 2005. The service takes a few input parameters, some "magic" occurs, and then returns what is basically a transformed ver...

Get Common Rows Within The Same Table

I've had a bit of a search, but didn't find anything quite like what I'm trying to achieve. Basically, I'm trying to find a similarity between two users' voting habits. I have a table storing each individual vote made, which stores: voteID itemID (the item the vote is attached to) userID (the user who voted) direction (whethe...

Best way to save data in a Java application?

Hi. I'm trying to find the best way to save the state of a simple application. From a DB point-of-view there are 4/5 tables with date fields and relationships off course. Because the app is simple, and I want the user to have the option of moving the data around (usb pen, dropbox, etc), I wanted to put all data in a single file. What ...

How do I model data that slowly changes over time?

Let's say I'm getting a large (2 million rows?) amount of data that's supposed to be static and unchanging. Supposed to be. And this data gets republished monthly. What methods are available to 1) be aware of what data points have changed from month to month and 2) consume the data given a point in time? Solution 1) Naively save every s...

Some data changes in the database. How can I trigger some C# code doing some work upon these changes?

Suppose I have some application A with a database. Now I want to add another application B, which should keep track of the database changes of application A. Application B should do some calculations, when data has changed. There is no direct communication between both applications. Both can only see the database. The basic problem is: ...

How to best represent items with variable # of attributes in a database?

Lets say you want to create a listing of widgets The Widget Manufacturers all create widgets with different number and types of attributes. And the Widget sellers all have different preferences on what type and number of attributes they want to store in the database and display. The problem here now is that each time you add in a new w...

Databases vs. plain text

When dealing with small projects, what do you feel is the break even point for storing data in simple text files, hash tables, etc., versus using a real database? For small projects with simple data management requirements, a real database is unnecessary complexity and violates YAGNI. However, at some point the complexity of a database...

Microsoft SQL Server 2005/2008: XML vs text/varchar data type

Does it have any sense (except of server side validation XML/schema/dtd) to store XML in XML type instead of text/varchar/ntext? I'm not planning to do any XML manipulation on database side. Purpose of my investigation is to decrease database size. Can a using an XML data type for untyped XML for the purpose? Which other pros and cons i...

Are Concurrent SQL inserts into the same table transactionally safe ?

I have a simple table in MySql whose raison-d'être is to store logs. The table has an autoincremented sequence and all the other columns has zero referential integrity to other tables. There are no unique keys or indexes on any columns. The column with autoincrement is the primary key. Will concurrent INSERTs ever interfere with each ot...

Why can application developers do datasebase stuff but database developers try to stay clear of application stuff ?

In my experience, this has been a contentious issue between "backend" (database developer) and "frontend" guys (application developer, client and server side). There have been many heated pub discussions on this subject. I just want to know is it just people have different mindsets, or lazy to learn more and feel comfortable in what ...

Mnesia transactions

hello accidentally some code that I'm writing is slowly turning into a DB system on its own, with incremental indexing, freeform "documents" (aka CouchDB kind) which can have arbitrary properties... annyyywaay... I decided to keep evolving it, mainly for educational purposes, and also to really tightly customize it just for my needs and ...

Database framework developing

Hello everyone, I'm developing (another) java web framework for personal use, and in addition to that I also want to develop somekind of persistence framework. I have already developed and engine that in order to access the table, you must only extend a class and create your fields with the same type and name of those in the table. Ex:...

Should I clone or denormalize my database for portable use?

I have a database that has lots of data and is all "neat", normalized (within reason - using EAV), and I have stored procedures to access and modify the data. I also have a WinForms application that users download to search and view this data (no inserts). To make things handy for use and updates, I've been using SQLite to store this da...

Getting column length from Hibernate mappings?

To validate data I am receiving I need to make sure that the length is not going to exceeded a database column length. Now all the length information is stored in the Hibernate mapping files, is there anyway to access this information programmatically? ...

displaying # views on a page without hitting database all the time

More and more sites are displaying the number of views (and clicks like on dzone.com) certain pages receive. What is the best practice for keeping track of view #'s without hitting the database every load? I have a bunch of potential ideas on how to do this in my head but none of them seem viable. Thanks, first time user. ...