CODEWITHSUNDEEP
sqldesign-patternsdatabase-designnaming
Ash Machine
Ash Machine

Reputation: 9901

Database design pattern where one attribute only applies if another attribute has certain value(s)

I see this design pattern often but don't have a name for it. I am curious to know what designers call this pattern and how they approach it with a sensible DB design.

As a simple example, say Students have a Grade Level (1 -8). If the student has a Grade Level of 7 or 8, then they may be an Honors student. So how do you track this bit flag against the student with knowledge that it only applies to Students of certain grade levels?

Upvotes: 1

Views: 350

Answers (2)

Daniel Lyons
Daniel Lyons

Reputation: 22803

I would call this a data dependency. Not all data dependencies can be modeled directly or conveniently with relational decomposition. This one can be handled pretty easily with a check constraint:

CREATE TABLE Students (
  id SERIAL PRIMARY KEY, -- for example, something else in reality
  grade INTEGER NOT NULL,
  honors BOOLEAN,
  CONSTRAINT ensure_honors_grade 
    CHECK((honors IS NULL AND grade < 7) OR 
          (honors IS NOT NULL AND grade >= 7))
);

Another solution might be to use two tables:

CREATE TABLE Students (
  id SERIAL PRIMARY KEY,
  grade INTEGER NOT NULL,
  CONSTRAINT id_grade_unique UNIQUE (id, grade) -- needed for FK constraint below
);

CREATE TABLE Honors (
  student_id INTEGER NOT NULL,
  grade INTEGER NOT NULL,
  honors BOOLEAN NOT NULL,
  CONSTRAINT student_fk FOREIGN KEY (student_id, grade) REFERENCES Students(id, grade),
  CONSTRAINT valid_grade CHECK(grade >= 7)
);

This alternative design is more explicit about the relationship between the grade and whether or not there is an honors flag, and leaves room for further differentiation of students in grades 7-8 (though the table name should be improved). If you only have the one property, the honors boolean, then this is probably overkill. As @BrankoDimitrijevic mentions, this doesn't enforce the existence of a row in Honors just because the grade is 7 or 8, and you're also paying for an index you wouldn't otherwise need. So there are tradeoffs; these are certainly not the only two designs possible; Branko also suggests using triggers.

When it comes to OO design, @Ryan is correct, but for proper relational database design one does not, in general, approach problems by trying to identify inheritance patterns. That is the OO perspective. It will always be important to concern yourself with your access patterns and how your code will be getting at the data, but in relational database design, one strives for normalization and flexibility in the database first and the code second, because there will invariably be multiple codebases getting at the data and you want to ensure the data is always valid no matter how buggy the accessing code is.

Upvotes: 5

Ryan
Ryan

Reputation: 1797

You need to consider the class model and how it is being used. Do you see Honors as a boolean flag for a Student? Such as if (grade > 6) Student.honor = true?

In general, you could consider this as a case of inheritance mapping. See it as Student is the parent class and your subclass is HonorStudent. There are multiple ways to map inheritance in a relational database.

You could map everything to a single table as in all the attributes from the base class and subclasses would be present but some of them would be set to NULL or empty. For example if HonorStudent had an attribute X, you would set attribute X to NULL when mapping a Student to the table.

Another case would be to have a table for each concrete class. So you would have a separate table for Student and HonorStudent.

You could also have a table for Student and HonorStudent with their own attributes. Subclasses would only have a table with the attributes they have (or add) as in attributes OTHER than inherited attributes. Inherited attributes would be mapped to the parent table and linked to with a unique id.

Not sure if this is the answer you were looking for but that's how I would look at it.

Upvotes: 0

Related Questions