0

I'm trying to model the following relationships between entities, mainly consisting of a partial, disjoint generalization.

original EERD

1

'mapped' to relational

2

Since I didn't need the subclasses to have any particular attributes I decided to use the "single table inheritance" approach, added the "type" field and moved the relationships towards the parent. After that I had two choices to make:

1- type for the "business type" attribute

2- way to constraint participation to at most one of the 4 relationships based on the type attribute

For the sake of portability and extensibility I decided to implement no.1 as a lookup table (rather than enum or a hardcoded check).

About no.2 I figured the best way to enforce participation and exclusivity constraints on the four relationships would be a trigger.

The problem is that now I'm not really sure how to write a trigger function; for instance it would have to reference values inserted into business type, so I'd have to make sure those can't be changed or deleted in the future. I feel like I'm doing something wrong so I wanted to ask for feedback before going further; is this a suitable approach in your opinion?

vimuth
  • 5,064
  • 33
  • 79
  • 116
Afelium
  • 1
  • 3

1 Answers1

0

I found an interesting article describing a possible solution: it feels a bit like an 'hack' but it should be working (it's intended for SQL Server, but it can be easily applied in postgres too).

EDIT: It consists in adding a type field to the parent table, and then have every child table reference said field along with the parent's id by using a foreign key constraint (a UNIQUE constraint on this pair of fields has to be added beforehand, since FKs must be unique).

Now in order to force the type field to match the table it belongs to, one adds a check constraint/always generated value ensuring that the type column always has the same value (eg: CHECK(Business_type_id = 1) in the Husbandry table, where 1 represents 'husbandry' in the type lookup table).

The only issue is that it requires a whole column in every subclass, each containing the same generated value repeated over and over (waste of space?), and it may fall apart as soon as the IDs in the lookup table are modified

Afelium
  • 1
  • 3
  • Welcome Afelium! It looks like you are a new member, I want to remind you than when posting answers with links; it's always recommended to describe the answer in a few words as the link can always be removed or unreachable ... Wich will leave you with non comprehensive answer – Rabhi salim Sep 05 '22 at 14:35