Converting n to m relationship with specialization to relational model

Question

I'm finding the best way to convert an eer diagram to the corresponding relational diagram. I have a generalization entity with some specializations which have separate relationships with other entities. The generalization entity has in turn a n-to-m relationsip with an entity. The following drawing clarifies the situation: Eer diagram with specialization and n-to-m relationship.

As the two specialized entities have separate relationships, I should convert them to two separate tables. Meanwhile, I should create a table modeling the n-to-m relationship which relates the entity 'User' to the entity 'Newsletter' (or better, its specializations). How to cope with this problem? I've not found any useful information.

The only possible solution I thought to was to create two separate tables modeling the n-to-m relationship, one linked to 'User' and 'Programming newsletter' tables, one linked to 'User' and 'Travel newsletter' tables. But I'm looking for opinions for that.

Answer 1

I think there are a couple of ways to go about this.

The simplest one, would be to break the assumption "As the two specialized entities have separate relationships, I should convert them to two separate tables". If you keep your specialisations together in a single table, you can use STI (Single table inheritance) for your generalisation. This approach has a drawback though, which is that your table will have many NULL values for those relationships that do not belong to the concrete specialisation.

The other approach, would be to use CTI (Class Table Inheritance). This approach assumes that there will be a specific table for each specialisation of your generalisation. This would get around the NULL problems, but it can potentially introduce a performance problem due to the fact that your code will need to eagerly join from the generalisation table to the specialisation on almost every single query you make to retrieve them.

I don't quite see the issue in the n-to-m relationship between User and Newsletter. You should be able to have a regular intermediate table that creates the association between the two, since there are no further attributes that complement that relationship.

Answer 2

I see no problem. I would implement your diagram using the following tables:

User (nickname PK, name, address)
Newsletter (name PK, supervisor, type)
Subscription (user_nickname PK/FK, newsletter_name PK/FK)
Programming_Newsletter (newsletter_name PK/FK, type FK, language)
Travel_Newsletter (newsletter_name PK/FK, type FK, means_of_transport)

I probably wouldn't use user nicknames / newsletter names as keys since I prefer stable compact identifiers, but that's another topic.