CODEWITHSUNDEEP
databaseentity-relationship
Rubiks
Rubiks

Reputation: 481

Mapping subclasses in ternary relationships

Is it alright for an attribute in a composite primary key in an intermediate table to be null? I have a ternary relationship and two of the entities have subclasses. The subclasses have their own unique primary keys. The intermediate table’s primary key consists of the subclass primary keys (see image below), so there are times when some of the attributes would be null.

I made orders into an intermediate table. You can see there will be times when these attributes would be null. For instance a customer orders something from the restaurant menu then catering order id would be null. Is it alright to it do this way? If not how should it be done?

enter image description here

Upvotes: 0

Views: 344

Answers (1)

dmfay
dmfay

Reputation: 2477

Composite key fields can't be null. customer and menu should be hiding their respective abstractions so that orders don't have a hard dependency on a particular type of customer or menu. If you need an intermediate table, give it a synthetic primary key and have it handle the distinction between restaurant and catering menus. Use table inheritance (if you've got Postgres) or views to represent the full data sets for each type of customer or menu.

But first make sure you really need to split those tables up -- it's okay for foreign keys to be null, but fewer tables and foreign keys in the first place make the application logic easier.

Upvotes: 2

Related Questions