DBA | Architecture and Data Blog

Automated Tablespace deployment

Allowing for differences in database configuration in different environment

In development mode you don’t want to worry about which table goes into what Tablespace in production as it complicates development environments. The production DBA’s want to have their input and control over deciding what table goes into what Tablespace. To allow for this I used a mapping scheme as shown below.

Lets assume we have 3 tables in our system Customer, CustomerOrder, OrderStatus. Where we are expecting Customer table to have large numbers of rows and CustomerOrder to have significanly large number of rows while OrderStatus would have few rows and not change as much. In development environments all these tables and their indexes will be put under the same tablespace. In production like environments we want to put them into seperate tablespaces.

Posted by Pramod Sadalage on Monday, May 1, 2006

To allow NULLs or NOT

When to allow NULLs or NOT, is critical domain discussion

Database designs I have seen tend to not constrain the data in the database. For example make the Item.ManufacturerID non-nullable and make it a foreign key to the Manufacturer table. Similarly Manufacturer.Name and Item.Rate as non-nullable columns. In any greenfield application (existing production application is a topic for another post). When you design table(s) lets say you have Item and Manufacturer table as shown below

CREATE TABLE Item
(ItemID NUMBER NOT NULL,
ManufacturerID NUMBER,
Name VARCHAR2(128),
Rate NUMBER,
CONSTRAINT PK_ITEM
              PRIMARY KEY (ItemID)
);

CREATE TABLE Manufacturer
(ManufacturerID NUMBER NOT NULL,
Name VARCAHR2(128),
CONSTRAINT PK_MANUFACTURER
              PRIMARY KEY (ManufacturerID)
);

For now let’s talk about NOT NULL constraint. Many say they don’t make columns non-nullable, because they don’t know the requirements at design time, or they don’t want their tests to create elaborate sets of data, or that the application enforces the constraint then why enforce the constraint on the database?

Posted by Pramod Sadalage on Wednesday, February 8, 2006

Refactoring Data

Many a times Refactoring is talked about in the context of code, recently I finished working with Scott Ambler on Database Refactoring

Lately I have been working on changing data in an production database, and have been wondering how do I define it, Data Refactoring? what are the patterns of Data Refactoring. First let me talk about what I mean by Data Refactoring.

When a given application goes into production, and starts life as a live application we find bugs with the application, these bugs create a weird data in the database, also with the way people change data through the app and some times through the database (yikes) and these data changes do lead to bad data. How do you go about fixing these data problems, are there patterns to these fixes.

Posted by Pramod Sadalage on Thursday, January 19, 2006

Automated Tablespace deployment

Allowing for differences in database configuration in different environment

To allow NULLs or NOT

When to allow NULLs or NOT, is critical domain discussion

Refactoring Data

FEATURED TAGS