Design | Architecture and Data Blog

Create an Index for all FK Columns in the database

Generating indexes for all foreign key constraints in the database

Most of the time I have seen database foreign key constraints on tables without indexes on those columns. Lets say the application is trying to delete a row from the CUSTOMER table DELETE FROM CUSTOMER WHERE CUSTOMERID = 1000; When the database goes about deleting the customerId of 1000, if there are foreign key constraints defined on customerId, then the database is going to try to find if the customerId of 1000 is used in any of those tables.

Posted by Pramod Sadalage on Thursday, September 3, 2009

Storing just the time in Oracle

How to store time, without the data component

We came across a need to save just the Time in the database, the requirement is to store time of the day, like say the user likes to have Breakfast at 8.15AM and Lunch at 12.32PM etc. Off course oracle does not have a time only data type. So we ended up using DATE as the data type and just setting the time. for example: CREATE TABLE FOO (PREFERRED_TIME DATE NULL); INSERT INTO FOO (TO_DATE('11:34','HH24:MI')); oracle automatically sets the date to the first day of the current month.

Posted by Pramod Sadalage on Thursday, February 12, 2009

Considerations when using stored procedures for doing CRUD

How to use stored procedures as interface to the data

Some environments like to have access to the database tables routed via stored procedures. Instead of using Create/Read/Update/Delete (CRUD) with DML, stored procedures are invoked with the parameters to perform the required operation. I’m not arguing about the benefits/pitfalls of this approach, if you have to do stored procedures, here are some things to look at. Make the stored procedure handle one object/table only and not multiple objects or tables. Do not commit open transactions inside the stored procedures.

Posted by Pramod Sadalage on Tuesday, February 3, 2009

Create a Index for every Foreign Key constraint created

Foreign key constraints need indexes for efficient enforcement

When creating a Foreign Key constraint on the database as shown below ALTER TABLE BOOK ADD (CONSTRAINT FK_BOOK_AUTHOR FOREIGN KEY (AUTHORID) REFERENCES AUTHOR) / In the above example we are telling the database to check if the BOOK.AUTHORID is a valid value in the Author.AuthorID. When the Author table is being changed, the database does data verification on the BOOK table using SELECT against the BOOK table for the AUTHORID some thing like this

Posted by Pramod Sadalage on Tuesday, July 15, 2008

Writing a SQL to generate a SQL

Generating SQL code using SQL

We had a weird requirement on our project recently..

Find all the Rows in All the tables that do not comply with the Constraints that we have in development but not in QA environments

Posted by Pramod Sadalage on Tuesday, May 6, 2008

Lessons from Re-Learning

Returning back to code that you worked long time back

I have been working on a project that I had worked in 2005, trying to get a handle on what I had done about 3 years back. Exploring code and the database has been fun, also discovering the data layout and building new set of data for production has been extremely entertaining. What I learnt from this whole experience was this if your code(application or other wise) is not expecting data, this data should not be provided or even considered valid by the database, the database should be designed such that it does not even allow invalid combinations of the data.

Posted by Pramod Sadalage on Thursday, December 27, 2007

Why do Evolutionary Design

What are the motivations for doing evolutionary design?

Why do Evolutionary Design or Iterative Design or Incremental Design? Everyone who has not worked in an evolutionary manner asks this? My answer, if you think the system you designed is NOT GOING TO CHANGE EVER then sure you can do design once and deploy once and you are done, move on to next project. But tell me one project you have been on, that does not have any changes in requirements, changes in technology, changes in look and feel etc after it was deployed.

Posted by Pramod Sadalage on Monday, November 5, 2007

Frameworks should not constrain your table design

Recently talking to someone about a persistence framework that they are using, this framework creates a rows in the database table with just Primary Key value and then later on updates the table row with values for other columns. Because of this framework, the tables cannot have any NOT-NULL constraints defined. For example, the framework is doing INSERT INTO customer (customerId, name, birthdate) VALUES (1,null,null); UPDATE customer set name = 'FOO Name', birthdate = '12/12/1978' WHERE customerId = 1; You cannot have NON-NULL constraint defined on name or birthdate column, since the INSERT statement would blow up, forcing you to change table design

Posted by Pramod Sadalage on Wednesday, October 3, 2007

When does evolutionary design happen?

What part of the product, project, iteration, story or day does evolutionary design happen?

A question I get, mostly related to the evolutionary database design and development. When the pair (team) gets a new feature (story) to work on, the team looks at the existing table/database design and sees if the current design is enough to implement the feature they are working on. If the currency database design does support the feature they are trying to implement, then they do not have to change the database at all, they will move on to implement the feature and change the application code as necessary.

Posted by Pramod Sadalage on Tuesday, August 28, 2007

Enforcing your assumptions about database functionality

Testing the behavior of the database

When you are writing xUnit tests you are in certain ways trying to make sure that the test breaks when the code that is being tested changes the assumptions you made when writing the Test and Production code. Similarly if you are relying on the database to throw a error when you put invalid data, then you should write a test around this assumption, so that when someone changes the database to not behave the way you assumed it to behave, the test you wrote will break and it will force the team to think about the change to the database that is being undertaken.

Posted by Pramod Sadalage on Friday, June 8, 2007