mistakes made in DDL script and lessons learned

Last year, I got the opportunity to create a java web application from scratch. Here, I want to talk about what I should have done differently when first DDL script for the app was created.

I'm correcting all these things now, but this is the technical debt that could have been avoided.

note: some of the jargons might be oracle specific

1. Create separate tablespaces for table, index and lobs.
This is a standard practice and helps DBAs in a lot of ways with management of db. The one I created had USERS as its default tablespace and none of CREATE TABLE, INDEX etc statements said anything about tablespace and everything resulted in USERS tablespace.

2. Give upfront thought to version upgradation from db perspective.
Once your application goes to production, and db gets loaded with a lot of data. It becomes rather difficult to upgrade schema from previous version of application to newer one. Its very important to have your migration strategy thought through and in place from the beginning itself.

3. Do not ignore "ON DELETE" clause when creating foreign key constraint.
In Oracle, you got two options here - ON DELETE SET NULL, ON DELETE CASCADE
you should be choosing one over the other based on how you want your data to evolve for the table.

4. Keep two users to manage your db.
One is the schema owner who runs all the DDLs and another one the schema user who has just enough privileges to run the application. Application should use the "schema user" user only to save application cdoe from getting any unwanted access to the db. Typically, schema user will need following privileges.

- CREATE SESSION
- CREATE SYNONYM
- SELECT/UPDATE/INSERT/DELETE privilege on all tables
- SELECT privilege on all SEQUENCEs