2

I know, foreign keys should uniquely identify rows and therefore reference a primary key.

However, consider my example: There are 4 pre-defined categories, and for some combinations of values for these categories, there is a hierarchy of modules. The hierarchical structure is implemented by a parent module column.

My approach would be to implement it in a single table with these columns:

  • category 1 (e.g., region)
  • category 2 (e.g., kind of school)
  • category 3 (e.g., class)
  • category 4 (e.g., subject)
  • module title
  • parent module title
  • and a primary key consisting of all categories + the module title.

I would want the parent module title to reference the module title column. Now because a foreign key needs to reference the whole primary key, I would need to insert columns like parent category 1, parent category 2, parent category 3, parent category 4. But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.

It would be ideal if I could write something like this in my table declaration:

--- fictitious SQL! ---

create table modules (
  ...,
  module_title varchar(50),
  foreign key parent_module_title varchar(50) 
    references modules(module_title) where 
      modules.category1 = category1 and modules.category2 = category2 and 
      modules.category3 = category3 and modules.category4 = category4
  )

Or something like:

--- fictitious SQL! ---

  ...
  parent_module_title varchar(50)
    check (exists 
    (select * from modules where modules.module_title = parent_module_title))
  ...

These are both in fictitious wishfulSQL (the latter throws cannot use subquery in check constraint), but is there a way of achieving my aim? I'm using Postgres if that matters.

5
  • As @LaurenzAlbe says - your schema looks dangerously like something called EAV - Entity-Attribute-Value - normally something to be avoided at all costs - recommed (just like Laurenz) that you refactor and split out your tables!
    – Vérace
    Apr 11, 2020 at 13:04
  • @ypercubeᵀᴹ yes that's what I need! Simple, but I didn't think of it. Do you want to post it as an answer?
    – David
    Apr 11, 2020 at 18:19
  • @verace I don't think I can split my table, it's a 5-ary relationship -.-
    – David
    Apr 11, 2020 at 18:20
  • It is not helpful to give invalid code to explain what you want, because it doesn't mean anything & we can't read your mind. You need to clearly say what you mean.
    – philipxy
    Apr 13, 2020 at 20:53
  • Every table with 5 columns represents a 5-ary relationship. That doesn't say anything about whether it can or should be decomposed or otherwise rearranged. Also "redundant" doesn't mean anything in particular & again having multiple appearances of subrows is neither bad nor OK per se. There is no point in wondering whether every pattern you see is bad especially when you are inexperienced/uninformed & especially when it's just because an everyday word applies that is like or reminds you of a technical term. Follow a published information modeling method.
    – philipxy
    Apr 13, 2020 at 21:08

3 Answers 3

1

Your problem is that your database design is not normalized at all. Split the data in several tables, like region, subject, module etc. so that each row in a table describes one of these entities.

Then it will be easy to establish foreign key relationships between the entities so that referential integrity is guaranteed.

2
  • Hm I do not actually store any attributes for the four categories, they all only have a unique name. I'm afraid, but it looks like I have a quinary many-to-many-to-many-to-many-to-one relationship between the four categories and the module name, and then the module name is in a parent relationship with itself. I've thought about it for a while but I couldn't come up with a more normalised schema.
    – David
    Apr 11, 2020 at 18:16
  • 1
    Even if it has only a name, you can keep it in a separate table. It sounds silly, but that way you could get your referential integrity. Also, you could make sure that the name is always the same that way, and you are not subject to typos. Apr 11, 2020 at 19:26
1

First a note about hierarchies. The technique you are currently trying to use for modules is called adjacency list. I would encourage you to look-up few other, more appropriate, SQL techniques like: path enumeration, nested sets, closure table.

However, something seems to be wrong with this sentence:

But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.

This sounds unusual at best, a hierarchy may look like:

1        region/
1.1      region/school/
1.1.1    region/school/class/
1.1.1.1  region/school/class/subject/

I will go out on a limb and claim that the problem here is not about foreign keys, but about implementing a hierarchy in SQL.

To simplify the problem, I would suggest to experiment with path enumeration for your project and explore other techniques if required.

-- Module titled TITLE is categorized as CAT_PATH.
--
module {TITLE, CAT_PATH}
    PK {TITLE}

Where CAT_PATH is one of:

  • region/
  • region/school/
  • region/school/class/
  • region/school/class/subject/

This will likely result in a different set of problems and questions, but those are going to be easier to solve.

2
  • Thanks a lot for your explanation modeling hierarchies, it's really instructive! In my case, however, the hierarchies are a bit different than you assumed, so I am sorry that I did not explain it clearly. Each region-kindofschool-class-subject has a hierarchy of modules which are taught there, so for example bavaria/highschool/12/math would have a hierarchy like 1 linear algebra, 1.1 matrices, 1.1.1 matrix notation. - But each region, kind of school, and of course class and subject has their own hierarchy.
    – David
    Apr 14, 2020 at 2:38
  • (The aim of the project [which is not yet addressed in this table] is to match these different hierarchies so that they can share learning material more easily between regions and different kinds of schools.)
    – David
    Apr 14, 2020 at 2:38
0

@ypercubeᵀᴹ gave a simple answer in their comment, which is exactly what I needed: It is possible to specify a foreign key from multiple columns to multiple columns.

You can have a FK from (cat1, cat2, cat3, cat4, parent_module_title) that REFERENCES (cat1, cat2, cat3, cat4, module_title).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.