Introduction
As a DBA, data modeling is part of the job; working with developers, reviewing table design, implementing proper constraints and much more. In an earlier post about performance tuning, I talk about some practical steps that can be taken to fix performance problems. However, sometimes those problems could be “self-inflicted” and I ended that post with the heading “No Amount of Tuning can fix that Schema” which is relevant to the content of this post.
In the following lines, I will share a recent lived experience about bad table design.
What happened?
I got a request from a developer to build a table that looked something like this:
Table name : project_approvals
COLUMN_NAME DATA_TYPE
------------ -------------
project_id NUMBER Primary key
,project_type VARCHAR2(32 CHAR)
,Project_role_1 VARCHAR2(32 CHAR)
,Project_role_2 VARCHAR2(32 CHAR)
,Project_role_3 VARCHAR2(32 CHAR)
,Project_role_4 VARCHAR2(32 CHAR)
,appr_order_1 VARCHAR2(32 CHAR)
,appr_order_2 VARCHAR2(32 CHAR)
,appr_order_3 VARCHAR2(32 CHAR)
,appr_order_4 VARCHAR2(32 CHAR)
,appr_active_1 VARCHAR2(02 CHAR)
,appr_active_2 VARCHAR2(02 CHAR)
,appr_active_3 VARCHAR2(02 CHAR)
,appr_active_4 VARCHAR2(02 CHAR)
,active VARCHAR2(01 CHAR)At the time, I had just watched a presentation referencing this exact same scenario. Obviously I was not okay with this design because of the following reasons:
- It is modeling a repeating group of up to 4
appr-roleassignments in separate columns. - It is storing the same kind of data in multiple numbered columns.
- It will cause difficult maintenance as Business rules could change, and the requirement could be for 5,6,7 or even 10 roles. Every time this happens, the table structure would need to change!
- Probably the biggest one, Messy queries! To get count of active roles, we’d need to unpivot columns or write repetitive SQL.
Example problem; Compute number of active roles
Suppose we want to answer a supposedly “simple” question from this table like “How many roles are currently active?”. Let’s create the table as requested and see how we can write this query.
We create the table as requested:
CREATE TABLE project_approvals
( project_id NUMBER
, project_type VARCHAR2(32)
, project_role_1 VARCHAR2(32)
, project_role_2 VARCHAR2(32)
, project_role_3 VARCHAR2(32)
, project_role_4 VARCHAR2(32)
, appr_order_1 VARCHAR2(32)
, appr_order_2 VARCHAR2(32)
, appr_order_3 VARCHAR2(32)
, appr_order_4 VARCHAR2(32)
, appr_active_1 VARCHAR2(2)
, appr_active_2 VARCHAR2(2)
, appr_active_3 VARCHAR2(2)
, appr_active_4 VARCHAR2(2)
, active VARCHAR2(1)
, CONSTRAINT project_approvals_pk PRIMARY KEY (project_id)
)
/
Table created.We insert 5 rows:
INSERT INTO project_approvals
VALUES
(1,'FINANCE','APPROVER','MANAGER','DIRECTOR',NULL,'1','2','3',NULL,'Y','Y','N',NULL,'Y')
,(2,'HR','LEAD','MANAGER','DIRECTOR',NULL,'1','2','3',NULL,'Y','Y','Y',NULL,'Y')
,(3,'IT','APPROVER','LEAD','VP',NULL,'1','2','3',NULL,'Y','N','Y',NULL,'Y')
,(4,'OPS','MANAGER','DIRECTOR','VP',NULL,'1','2','3',NULL,'Y','N','N',NULL,'N')
,(5,'SALES','APPROVER','MANAGER','VP',NULL,'1','2','3',NULL,'Y','Y','Y', NULL,'Y' );
COMMIT;Notice the use of the values Constructor clause, new in Oracle AI Database 26ai.
If we want to write a query to see the number of active roles, we can use a CASE statement:
SELECT
SUM(
CASE WHEN appr_active_1 = 'Y' THEN 1 ELSE 0 END +
CASE WHEN appr_active_2 = 'Y' THEN 1 ELSE 0 END +
CASE WHEN appr_active_3 = 'Y' THEN 1 ELSE 0 END +
CASE WHEN appr_active_4 = 'Y' THEN 1 ELSE 0 END
) AS active_role_count
FROM project_approvals;
ACTIVE_ROLE_COUNT
____________________
11
Another way would be to use UNPIVOT:
SELECT
SUM(COUNT(appr_active)) AS active_role_count
FROM (
SELECT project_id,
project_type,
appr_active
FROM project_approvals
UNPIVOT (
appr_active FOR role_col IN (
appr_active_1 AS '1',
appr_active_2 AS '2',
appr_active_3 AS '3',
appr_active_4 AS '4'
)
)
)
WHERE appr_active = 'Y'
GROUP BY project_id, project_type
ORDER BY project_id ;
ACTIVE_ROLE_COUNT
____________________
11 This query should be a simple count! However, In both solutions shown, the queries are unnecessarily complex. This is because our table design is not great.
A better Design
Instead of having one table, we can create 3 tables named:
project_approval_headerproject_rolesproject_approval_details
The code below creates the tables:
CREATE TABLE project_approval_header
( project_approval_header_id NUMBER
,project_type VARCHAR2(32 CHAR)
,active CHAR(1)
,CONSTRAINT project_approval_header_pk PRIMARY KEY (project_approval_header_id)
);
Table PROJECT_APPROVAL_HEADER created.
CREATE TABLE project_roles (
role_id NUMBER
,role_name VARCHAR2(32 CHAR)
,CONSTRAINT project_roles_pk PRIMARY KEY (role_id)
);
Table PROJECT_ROLES created.
CREATE TABLE project_approval_details (
project_approval_details_id NUMBER
,project_approval_header_id NUMBER
,role_id NUMBER
,appr_order NUMBER
,active CHAR(1)
,CONSTRAINT project_approval_details_pk PRIMARY KEY (project_approval_details_id)
,CONSTRAINT project_approval_details_fk1 FOREIGN KEY (project_approval_header_id)
REFERENCES project_approval_header(project_approval_header_id)
,CONSTRAINT project_approval_details_fk2 FOREIGN KEY (role_id)
REFERENCES project_roles(role_id)
,CONSTRAINT project_approval_details_uk1
UNIQUE (project_approval_header_id, appr_order)
);
Table PROJECT_APPROVAL_DETAILS created.
Why is this better?
- With this design, we can insert any number of roles without modifying the table structure.
- Proper referential Integrity Constraints can actually be defined on this model.
- It’s much simpler to query from these tables.
- Whenever business rules change, the table does not need to be modified and thus it’s easier to maintain.
Compute number of active roles again
Let’s insert data into the tables:
--project_approval_header
INSERT INTO project_approval_header
(project_approval_header_id, project_type, active)
VALUES (1, 'FINANCE', 'Y')
,(2, 'HR', 'Y')
,(3, 'IT', 'Y')
,(4, 'OPS', 'N')
,(5, 'SALES', 'Y');
--roles
INSERT INTO project_roles (role_id, role_name) VALUES (1, 'APPROVER');
INSERT INTO project_roles (role_id, role_name) VALUES (2, 'MANAGER');
INSERT INTO project_roles (role_id, role_name) VALUES (3, 'DIRECTOR');
INSERT INTO project_roles (role_id, role_name) VALUES (4, 'VP');
INSERT INTO project_roles (role_id, role_name) VALUES (5, 'LEAD');
--project_approval details
INSERT INTO project_approval_details
(project_approval_details_id, project_approval_header_id, role_id, appr_order, active)
VALUES (1, 1, 1, 1, 'Y')
,(2, 1, 2, 2, 'Y')
,(3, 1, 3, 3, 'N')
,(4, 2, 5, 1, 'Y')
,(5, 2, 2, 2, 'Y')
,(6, 2, 3, 3, 'Y')
,(7, 3, 1, 1, 'Y')
,(8, 3, 5, 2, 'N')
,(9, 3, 4, 3, 'Y')
,(10, 4, 2, 1, 'Y')
,(11, 4, 3, 2, 'N')
,(12, 4, 4, 3, 'N')
,(13, 5, 1, 1, 'Y')
,(14, 5, 2, 2, 'Y')
,(15, 5, 4, 3, 'Y');
COMMIT;Now say we wanted to run the query to see the total number of active roles again. We only need to query the project_approval_details table:
SELECT COUNT(*) AS active_role_count
FROM project_approval_details
WHERE active = 'Y';
ACTIVE_ROLE_COUNT
____________________
11 It’s that simple; A straightforward COUNT(*) to get the same results as the queries with the CASE statement and UNPIVOT. With these second set of tables, it’s much easier to query our data with much simpler SQL.
Conclusion
In the world of AI we live in today, everyone seems to want fancy tools. While this is great, the fundamentals are still very… fundamental!
Bad database design like the one shown in this example could lead to complex queries to extract basic information. Consequently, this could cause poor performance if the entire application is made up of these complex queries. Additionally, perhaps a lot of tuning efforts could go into fixing or rewriting the resulting queries down the road. This is all wasted cost in terms of time and resources meanwhile the real issue is poor database design.
Normalization, a fundamental principle of database design and data modelling, is sometimes seen as one more thing to do. However, if you’re using a relational database like Oracle, properly designing schemas and following best practices for normalization could be the difference between your project succeeding and it failing woefully. Don’t forget the fundamentals, they matter… a lot!