database - Javier Neira on Diigo

09 Dec 09

2. Do batch update

You can send multiple queries to the database at a time using batch update

feature of statement objects this reduces the number of JDBC calls and improves

performance. Here is an example of how you can do batch update,

statement.addBatch( "sql query1");

statement.addBatch(" sql query2");

statement.addBatch(" sql query3");

statement.executeBatch();

All three types of statements have these methods to do batch update.

23 Nov 09

Richard Dingwall » The trouble with soft delete

richarddingwall.name/...the-trouble-with-soft-delete - Preview

database softd delete bestpractices data business

First is the standard deleted flag to indicate an item should be ignored:
nother style uses meaningful status codes:
20 more annotations...
- a fixed lifetime that starts and ends at a specific time (it might not have started yet):
- soft delete columns are easy to implement, but incur a cost to query complexity and database performance later down the track.
- It’s like a tax; a mandatory WHERE clause to ensure you don’t return any deleted rows.
- It’s very simple to do, but if you forget to do it in even one place, bugs can creep in very fast.
- I’ve found that most RDBMSs are actually pretty good at recognizing soft delete columns (probably because they are so commonly used) and does a good job at optimizing queries that use them
- It has around five million rows in total, but of that, only a very small percentage (2%) are still active and interesting to the application. The rest are all historical;
- the reasons why you might want to implement soft deletes in a database.
- To provide an ‘undelete’ feature.
  Auditing.
  For soft create.
  To keep historical items.
- two styles
- There is an undelete feature available somewhere in the UI, or
  Undelete requires running commands directly against the database.
- If there is an undo delete button available somewhere for users, then it is an important use case that needs to be factored into your code.
- you can just get them from the previous night’s backup.
- to keep a record of when an item was deleted, and who deleted it
- One possible conclusion from this thought is that you simply log all DML operations on the table. You can do this pretty easily with triggers
- The soft delete pattern can also be extended for item activation — instead of simply creating an item as part of the active set, you create it in an inactive state and flick a switch or set a date for when it should become active.
- We need to keep this data somewhere, but it’s no longer immediately interesting to the application because either
- haven’t included deleted items here because there are very few cases I can think of in business applications where data is simply deleted (unless it was entered in error) — usually it is just transformed from one state to another. Udi Dahan explains this well in his article Don’t Delete — Just’ Don’t:
  
  Orders aren’t deleted – they’re cancelled. There may also be fees incurred if the order is canceled too late.
  Employees aren’t deleted – they’re fired (or possibly retired). A compensation package often needs to be handled.
  
  Jobs aren’t deleted – they’re filled (or their requisition is revoked).
- instead of lumping differently-used sets of items together in one huge table with a flag or status code as a discriminator, consider splitting them up into separate tables.
- these guidelines
- Primary transactional tables should only contain data that is valid and active right now.
  Do you really need to be able to undo deletes? If so, there are dedicated patterns to handle this.
  Audit logging at the row level sucks. Do it higher up where you know the full story.
  If a row doesn’t apply yet, put it in a queue until it does.
  Physically separate items in different states based on their query usage.

19 Oct 09

MapReduce Online! (and some gimmes) « Data Beta

Background:
Parallel database engines have always been able to stream out results to certain queries while they run. And a bunch of us did research over the years to leverage that feature to do more things, like online aggregation (for “early returns” from inherently batch jobs), and continuous queries (for processing infinitely streaming data inputs, producing infinitely streaming data outputs). So why not do the same for MapReduce engines? Why are they limited to batch processing?

databeta.wordpress.com/...mapreduce-online - Preview

database mapreduce parallelism research hadoop

Parallel database engines have always been able to stream out results to certain queries while they run. And a bunch of us did research over the years to leverage that feature to do more things, like online aggregation (for “early returns” from inherently batch jobs), and continuous queries (for processing infinitely streaming data inputs, producing infinitely streaming data outputs). So why not do the same for MapReduce engines? Why are they limited to batch processing?

17 Oct 09

Code Monkeyism: ORMs are a thing of the past

codemonkeyism.com/orms - Preview

orm database oop analysis dao

In the end, the structure of data is something fundamental that can’t be simplified or abstracted away. The ORM doesn’t resolve the impedance mismatch, it just postpones it.
You think the ORM works but it doesn’t. It throws exceptions to you.
2 more annotations...
- NoSQL data stores. Polyglot persistence
- Good DAOs with standard CRUD implementations help there. Just use Spring JDBC for databases.

07 Oct 09

Archives of the Caml mailing list > Message from Brian Hurt

caml.inria.fr/...d3a3985e50f9bd23013579.en.html - Preview

database bestpractices interop protocol

15 Sep 09

Modeling Data Associations in Clojure? - Clojure | Google Groups

Nested maps are a good way to start, but they're pretty low level as
you want to do more complicated things. If you're talking about data
associations, the relational model is higher level and it's really
worth modeling your data in that way.

Relational data manipulation doesn't require a sql database. If you
want to use an in memory data representation, clojure has a couple of
options. The clojure.set namespace [1] has some useful functions
operating on sets of maps, in a relational way. join, select, and
project will get you pretty far.

A more structured and powerful way of doing things is
clojure.contrib.datalog [2]. I haven't had a chance to play with this
(yet!), but it looks very cool. It's a functional approach and it's
more well integrated with the language than something generating sql.

If your app really does call for connection to an external db, then
there's no reason not to go with clj-record. You wouldn't be the first
person to make a SQL db backed web app. :) Using a database through
clojure feels a lot more natural than via an ORM.

groups.google.com/...263e38a71ac358fa - Preview

clojure business data modeling database

You can only

create a new map with some data reused. There lies the problem - how

to "update the references" to the new map at all the places that link

to it. My hunch tells me to use refs for that :)
Nested maps are a good way to start, but they're pretty low level as

you want to do more complicated things. If you're talking about data

associations, the relational model is higher level and it's really

worth modeling your data in that way.

Relational data manipulation doesn't require a sql database. If you

want to use an in memory data representation, clojure has a couple of

options. The clojure.set namespace [1] has some useful functions

operating on sets of maps, in a relational way. join, select, and

project will get you pretty far.

A more structured and powerful way of doing things is

clojure.contrib.datalog [2]. I haven't had a chance to play with this

(yet!), but it looks very cool. It's a functional approach and it's

more well integrated with the language than something generating sql.

If your app really does call for connection to an external db, then

there's no reason not to go with clj-record. You wouldn't be the first

person to make a SQL db backed web app. :) Using a database through

clojure feels a lot more natural than via an ORM.
1 more annotations...
- I think the simplest solution to this problem is to use functions
  
  instead of maps. That is, instead of defining your API in terms of
  
  maps with specific keys, define it in terms of functions that read/
  
  write individual fields.
  
  For example, you would have an opaque "Person" object, perhaps in a
  
  Ref, and functions like get-name, set-name, get-policy, etc. The
  
  underlying storage model can be whatever you want -- sets, SQL,
  
  files, .... You just have to train yourself to never touch a model
  
  object except through its defined API.

A Simple Guide to Five Normal Forms in Relational Database Theory

www.bkent.net/simple5.htm - Preview

normal form database guide codd

Fourth [5] and fifth [6] normal forms deal with multi-valued facts
The multi-valued
fact may correspond to a many-to-many relationship, as with employees and skills, or to a
many-to-one relationship, as with the children of an employee (assuming only one parent is
an employee). By "many-to-many" we mean that an employee may have several
skills, and a skill may belong to several employees.
4 more annotations...
- In a sense, fourth and fifth normal forms are also about composite keys.
- These normal
  forms attempt to minimize the number of fields involved in a composite key, as suggested
  by the examples to follow.
- Under fourth normal form, a record type should not contain two or more independent
  multi-valued facts about an entity. In addition, the record must satisfy third normal
  form.
- suppose that an employee could only exercise certain skills in certain
  languages. Perhaps Smith can cook French cuisine only, but can type in French, German, and
  Greek. Then the pairings of skills and languages becomes meaningful, and there is no
  longer an ambiguity of maintenance policies. In the present case, only the following form
  is correct:
  
  -------------------------------
  | EMPLOYEE | SKILL | LANGUAGE |
  |----------+-------+----------|
  | Smith | cook | French |
  | Smith | type | French |
  | Smith | type | German |
  | Smith | type | Greek |
  -------------------------------
  
  Thus the employee:skill and employee:language relationships are no longer independent.
  These records do not violate fourth normal form. When there is an interdependence among
  the relationships, then it is acceptable to represent them in a single record.

MySQL :: An Introduction to Database Normalization

dev.mysql.com/...intro-to-normalization.html - Preview

mysql database normalization tutorial codd

The first normal form (or 1NF) requires that the values in each column of a table are atomic. By atomic we mean that there are no sets of values within a column
One method for bringing a table into first normal form is to separate the entities contained in the table into separate tables
12 more annotations...
- surrogate primary key -- an artificial primary key used when a natural primary key is either unavailable or impractical.
- when using a surrogate primary key it is still important to create a UNIQUE key to ensure that duplicate records are not created inadvertently (but some would argue that if you need a UNIQUE key it would be better to stick to a composite primary key).
- There are various types of relationships that can exist between two tables:
  
  One to (Zero or) One
  One to (Zero or) Many
  Many to Many
- To represent a many-to-many relationship in a relational database we need a third table to serve as a link between the two.
- When we have a one-to-many relationship, we place a foreign key in the table representing the �many�, pointing to the primary key of the table representing the �one�.
- Columns in a table that refer to primary keys from another table are known as foreign keys, and serve the purpose of defining data relationships.
- the Second Normal Form (or 2NF) deals with relationships between composite key columns and non-key columns.
- The second normal form (or 2NF) any non-key columns must depend on the entire primary key. In the case of a composite primary key, this means that a non-key column cannot depend on only part of the composite key.
- To bring the Review table into compliance with 2NF, the Author_URL must be moved to the Author table.
- Third Normal Form (3NF) requires that all columns depend directly on the primary key. Tables violate the Third Normal Form when one column depends on another column, which in turn depends on the primary key (a transitive dependency).
- In the Publisher table the City and State fields are really dependent on the Zip column and not the Publisher_ID. To bring this table into compliance with Third Normal Form, we would need a table based on zip code:
- With our tables now separated by entity, we join the tables together in our SELECT queries and other statements to retrieve and manipulate related data

10 Sep 09

How FriendFeed uses MySQL to store schema-less data - Bret Taylor's blog

bret.appspot.com/...how-friendfeed-uses-mysql - Preview

mysql database performance scalability architecture nosql

making schema changes or adding indexes to a database with more than 10 - 20 million rows completely locks the database for hours at a time. Removing old indexes takes just as much time, and not removing them hurts performance because the database will continue to read and write to those unused blocks on every INSERT, pushing important blocks out of memory
We can change the "schema" simply by storing new properties.
3 more annotations...
- We index data in these entities by storing indexes in separate MySQL tables. If we want to index three properties in each entity, we will have three MySQL tables - one for each index.
- We can store new properties and index them in a day's time rather than a week's time, and we don't need to swap MySQL masters and slaves or do any other scary operational work to make it happen.
- Since our database is sharded, and indexes for an entity can be stored on different shards than the entities themselves, consistency is an issue

30 Jun 09

4 Steps To a Professional Database Design | ProgrammerFish - Everything that's programmed!

www.programmerfish.com/a-professional-database-design - Preview

database design bestpractices

29 May 09

mindstorms: Quick Reference to Alternative data storages

themindstorms.blogspot.com/...rence-to-alternative-data.html - Preview

database scalability alternative reference

21 Apr 09

Apache CouchDB: The CouchDB Project

couchdb.apache.org - Preview

database couchdb apache

16 Apr 09

memcached: a distributed memory object caching system

www.danga.com/memcached - Preview

cache performance distributed database

Mastering Grails: GORM: Funny name, serious technology

www.ibm.com/...j-grails02128 - Preview

orm grails java database intro

17 Mar 09

InfoQ: Eight Isolation Levels Every Web Developer Should Know

www.infoq.com/...eight-isolation-levels - Preview

database isolation webdev

03 Feb 09

iBatis Java Tutorial

cvs.apache.org/...ATIS-SqlMaps-2-Tutorial_en.pdf - Preview

tutorial java jdbc databinding database .net ibatis

02 Feb 09

Introduction to Data Mining

www-users.cs.umn.edu/...index.php - Preview

tutorial AI database datamining

09 Jan 09

InfoQ: Performance Anti-Patterns in Database-Driven Applications

In this article, Alois Reitbauer, a Performance Architect for dynaTrace Software, specifies several architectural anti-patterns which can downgrade an application’s performance. Knowing those anti-patterns and proactively designing the application to avoi

www.infoq.com/...Anti-Patterns-Alois-Reitbauer - Preview

patterns performance database

Javier Neira's Library tagged database → View Popular

PreciseJava.com - Best practices to improve performance in JDBC

Richard Dingwall » The trouble with soft delete

MapReduce Online! (and some gimmes) « Data Beta

Code Monkeyism: ORMs are a thing of the past

Archives of the Caml mailing list > Message from Brian Hurt

Modeling Data Associations in Clojure? - Clojure | Google Groups

A Simple Guide to Five Normal Forms in Relational Database Theory

MySQL :: An Introduction to Database Normalization

How FriendFeed uses MySQL to store schema-less data - Bret Taylor's blog

4 Steps To a Professional Database Design | ProgrammerFish - Everything that's programmed!

mindstorms: Quick Reference to Alternative data storages

Apache CouchDB: The CouchDB Project

memcached: a distributed memory object caching system

Mastering Grails: GORM: Funny name, serious technology

InfoQ: Eight Isolation Levels Every Web Developer Should Know

iBatis Java Tutorial

Introduction to Data Mining

InfoQ: Performance Anti-Patterns in Database-Driven Applications

Selected Tags

Related Tags

Sponsored Links

Top Contributors

Groups interested in database

Coders

Genealogy Research Resources

Nutrition

Online Databases

OracleDBA