0

I'm currently implementing a subscription system where a user can subscribe on an item that can be a type of either A, B or C.

There is table for each of the item types : ItemTypeA, ItemTypeB and ItemTypeC.

This is a many-to-many relation between the user table and one of the item type table.

User
---
id
name
email

I'm wondering what would be the best solution ?

1 - Each item type has its subscription table

SubscriptionToA    <--->   ItemTypeA
---                        ---
itemTypeAId                id
userId



SubscriptionToB    <--->   ItemTypeB
---                        ---
itemTypeBId                id
userId



SubscriptionToC    <--->   ItemTypeC
---                        ---
itemTypeCId                id
userId

2 - One subscription table with an extra field for the type

Subscription    <--->   ItemTypeA
---                     ---
itemTypeId              id
userId
type (= 'A')


Subscription    <--->   ItemTypeB
---                     ---
itemTypeId              id
userId
type (= 'B')


Subscription    <--->   ItemTypeC
---                     ---
itemTypeId              id
userId
type (= 'C')

Can you tell me what are the pros and cons for both solutions ?

7
  • There is able for each of the item types : ItemTypeA, ItemTypeB and ItemTypeC. According to your description this looks like de-normalizing. You must decide does these are instances of the same entity which differs in some attriblte (and maybe in some other attributes due to this attribute value), or these are absolutely independent and unrelated entities.
    – Akina
    Jan 15 at 9:15
  • @Akina Actually, ItemTypes are nested elements. C is contained by B which is contained by A. The deeper you subscribe to a item type, the more precise is the information you subscribed to.
    – Martial
    Jan 15 at 9:59
  • ItemTypes are nested elements. C is contained by B which is contained by A. I.e. you have a tree of nested objects.. If so I don't understand what is the reason for to have 3 separate tables instead of one table with inner parent-child relation..
    – Akina
    Jan 15 at 10:13
  • Because it's a many-to-one relation between them. (C is used by many B, etc.)
    – Martial
    Jan 15 at 10:37
  • ?? common parent-child.. parent B have many childs C.
    – Akina
    Jan 15 at 11:05

1 Answer 1

1

The second option is the best of the two presented here.

The first option presented leads to redundancy and increased maintenance overhead. Each table would require updates for any changes in subscription logic, raising the risk of errors.

From a reporting point of view to get a comprehensive view of a user's subscriptions, you'd need to pull data from multiple tables and merge the results. As the data base grows this increases the complexity of your application.

A more scalable design, such as a single subscriptions table that can adapt to new item types without structural changes, is generally preferable.

Your Answer

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

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