0

In a simple database design, I have a table called person which has following columns:

  id serial primary key,
  full_name varchar(100),
  city varchar(40),
  certifications TEXT[]

There is another table called certification which stores the list of certifications which has one column

  • certification_name TEXT primary key

I want to attribute these two table with a foreign key such that elements of certifications in person table refer to certification table. How to use a foreign key structure like this with an array? I use postgresql as database.

MobiusT
  • 43
  • 1
  • 7
  • 4
    No, that's not possible. Create a properly normalized model with a one-to-many relationship and you can easily define a FK constraint –  Jun 28 '21 at 11:32
  • even if it was possible, it would be a very bad idea, because you had to ensure, for the cartesian product, that one text is exactly like the other, which would completely kill the server performance. Do as recommended by @a_horse_with_no_name – shikida Jun 28 '21 at 11:38
  • 1
    You **do not have** a simple database design. I assume you come to this conclusion due to having few tables. However a simple database design is virtually immune to just the number of tables. Simplicity depends upon proper definition (ie normalization). Your person table contains a repeating group (an array) thus violating 1st normal form which makes it a complex design. So normalize you data as suggested by @a_horse_with_no_name. – Belayer Jun 28 '21 at 19:06

0 Answers0