CODEWITHSUNDEEP
database-designperformancenormalization
MicE
MicE

Reputation: 5128

"Extend" relationship in a relational database

Hello,
I'm currently trying to apply the most efficient way to store an "extend" relationship between entities in a relational database.

For the sake of example, lets say we have the following simplified entities:

User contains attributes which apply to both Student and Teacher. Both Student and Teacher contain custom attributes which are unique to them.

The first thing that comes to mind is to create a single table with columns for all singular data (i.e. except one-to-many fields):

User
-------------
User ID
First name
Last name
Student class
Teacher office no.
Teacher description
...

This however won't be very efficient from a storage perspective, because:


It would be more efficient to replicate relationships between the entities:

User
-------------
User ID
First name
Last name
...


Student
-------------
User ID
Student class
...


Teacher
-------------
User ID
Teacher office no.
Teacher description
...

So my questions are:

  1. Is the above concern taking it too far, i.e. should we leave storage efficiency to the database engine?
  2. Is splitting the entities into 3 tables still OK in terms of normalization?
  3. If it's not a good approach, how would you recommend to treat "extend" relationships in a relational database?

Thank you.

Upvotes: 2

Views: 2444

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

If a user can't be both a teacher and a student, then you're looking at a straightforward supertype/subtype problem. (I'm using supertype and subtype in their relational database design sense, not in their object-oriented programming sense.) You're right to store in "students" only those attributes that describe students, and to store in "teachers" only those attributes that describe teachers.

-- Supertype: users
create table users (
  user_id integer not null unique,
  user_type char(1) not null 
    check (user_type in ('T', 'S')),
  -- other user columns
  primary key (user_id, user_type)
);

-- Subtype: students
create table students_st (
  user_id integer not null,
  user_type char(1) not null default 'S'
    check (user_type = 'S'),
  locker_num integer not null unique 
    check (locker_num > 0),
  -- other student columns
  primary key (user_id, user_type),
  foreign key (user_id, user_type) 
    references users (user_id, user_type) 
    on delete cascade
);

-- Subtype: teachers
create table teachers_st (
  user_id integer not null,
  user_type char(1) not null default 'T'
    check (user_type = 'T'),
  office_num char(4),
  -- other teacher columns
  primary key (user_id, user_type),
  foreign key (user_id, user_type) 
    references users (user_id, user_type) 
    on delete cascade
);

create view teachers as 
select u.user_id,
       u.user_type,
       -- other user columns
       t.office_num
       -- other teacher columns
from users u
inner join teachers_st t on (u.user_id = t.user_id);

create view students as 
select u.user_id,
       u.user_type,
       -- other user columns
       s.locker_num
       -- other student columns
from users u
inner join students_st s on (u.user_id = s.user_id);

At this point, you'd also do whatever your dbms requires to make these two views updatable—triggers, rules, whatever. Application code inserts, updates, and deletes from the views, not from the base tables.

Upvotes: 3

Related Questions