Good Practices for Creation Scripts - Part 1

When you're working with small databases with just a few tables, everything is handled very fast and easy.

But what happens when our small application starts to grow and some modules appear and require more and more tables? Why not start doing things the right way to avoid future "complete reconstructions" due to incompatibilities?

So, here are some tips:

• For each table, always have a code (2 to 4 chars)

employee --> emp
department --> dep
title --> ttl
works_in --> wrk

• Each column in the table must contain the code of its parent table. Foreign keys will have the referenced table code.

In table employee:
id --> emp_id
name --> emp_name
title --> ttl_id

• Always write complete creation scripts. Avoid using simplified coding style, because it will generate constraints with system-generated names, hard to find and identify later on. When a constraint is violated, it’s good to have it named to avoid encounters with messages like “Constraint SYS00013292384 violated”.
  

I'll present 2 ways to create a very small employee database, make your own conclusions:

The short way:

CREATE TABLE title (
id NUMBER(10) PRIMARY KEY,
desc VARCHAR2(50) NOT NULL
);

CREATE TABLE employee (
id NUMBER(12) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
title NUMBER(10) NOT NULL
);
ALTER TABLE employee ADD FOREIGN KEY (title) REFERENCES title (id);

CREATE TABLE department (
id NUMBER(2) PRIMARY KEY,
name VARCHAR2(50) NOT NULL
);

CREATE TABLE works_in (
emp NUMBER(12) NOT NULL,
dep NUMBER(2) NOT NULL
);
ALTER TABLE works_in ADD PRIMARY KEY (emp,dep);
ALTER TABLE works_in ADD FOREIGN KEY (emp) REFERENCES employee (id);
ALTER TABLE works_in ADD FOREIGN KEY (dep) REFERENCES department (id);

The right way (for me) to save us trouble in the future (and start making good coding habits):

CREATE TABLE title
(
ttl_id NUMBER(2),
ttl_name VARCHAR(50)
);

ALTER TABLE title ADD CONSTRAINT "PK_ttl" PRIMARY KEY (ttl_id);


CREATE TABLE employee
(
emp_id NUMBER(12),
emp_name VARCHAR(50),
ttl_id NUMBER(2),
);

ALTER TABLE employee
ADD CONSTRAINT "PK_emp" PRIMARY KEY (emp_id);
ALTER TABLE employee
ADD CONSTRAINT "NN_emp_name" CHECK (emp_name IS NOT NULL);
ALTER TABLE employee
ADD CONSTRAINT "FK_emp_ttl" FOREIGN KEY (ttl_id)
REFERENCES title (ttl_id);


CREATE TABLE department
(
dep_id NUMBER(2),
dep_name VARCHAR(25),
dep_location VARCHAR(50)
);

ALTER TABLE department
ADD CONSTRAINT "PK_dep" PRIMARY KEY (dep_id);

CREATE TABLE works_in
(
emp_id NUMBER(12),
dep_id NUMBER(2)
);

ALTER TABLE works_in
ADD CONSTRAINT "PK_wrk" PRIMARY KEY (emp_id, dep_id);

ALTER TABLE works_in
ADD CONSTRAINT "FK_wrk_emp" FOREIGN KEY (emp_id)
REFERENCES employee (emp_id);

ALTER TABLE works_in
ADD CONSTRAINT "FK_wrk_dep" FOREIGN KEY (dep_id)
REFERENCES department (dep_id);

If you use these simple tips, you’ll not only work in a more organized way, you’ll be more efficient when solving problems over databases with these kind of implementation.

Once again, I’m not telling this is THE WAY to do things.  Just one way that works for me.  Comments are always welcome.