Dealing with 3NF in terms of database domain modelling where attributes are added knowing they create transitive dependency

Question

I am currently working on setting up a database domain model, where in terms of normalization I will be challenged due to transitive dependency. However, for this particular model it is a choice, that we choose to add such transitive dependency for a reason, and I am wondering how you would go about dealing with such cases in the aspect of normalization?

Let me show what I mean:

I have a table called UserSubscription that have the following attributes:

id {dbgenerated}
created
user
price
currency
subscriptionid

The values for:

price
currency

Depend on the subscriptionid, which points to a second table Subscription (in which the subscriptionid is a FK reference to this tables PK). One might say why, would I even consider including duplicate values from the Subscription table into the UserSubscription table? Well the reason is that the Subscription might change at any point in time, and for reference we want to store the original value of the subscription in the UserSubscription so that even if it changes we still have the values that the user signed up for originally.

I know from the perspective of normalization, that this transitive dependency I create should be fixed, and ideally I would move the values back into the subscription table, and just not allow the values to be modified, and instead create a new subscription whenever it is necessary.

But ideally I do not want to create new subscriptions every time something needs to change in those that exist, simply because it is expected these change often - following say market competition values. At the same time for every new subscription created any user will have more to choose from.

This also means that if we no longer want to use a subscription, we would need to: Remove it, and Create a new one. This can be fixed by simply Updating, since we will no longer need the old one anyways.

The above is a school project, I just wonder whether it would ever be "ok" in terms of normalization to choose such approach, when I choose to do so by choice, and to reduce the tasks associated with removing and creating new subscriptions when I expect these would change frequently.

question from:https://stackoverflow.com/questions/65870720/dealing-with-3nf-in-terms-of-database-domain-modelling-where-attributes-are-adde

Answer 1

why don't you instead create a M:N table (mapping table) USER_SUBSCRIPTION where you will have the relationships between USER and SUBSCRIPTION ? You can store all values there historically, and don't have to remove/create anything with the change.. it the user decides to opt-out, you only update the flag_active, flag_deleted, flag_dtime_end, whatever works for you...

Here is a simple model for demonstration:

USER
id_user PK
name
... other details

SUBSCRIPTION
id_subscription PK
name
details
flag_active (TRUE|FALSE or 1|0 values)
... other details

USER_SUBSCRIPTION
id_user               FK
id_subscription       FK
dtime_start       -- when the subscription started
dtime_end         -- when the subscription ended
flag_valid (T|F or 1|0)  -- optional, will give you a quick headsup about active subscriptions ... but this is sort of redundant, for you can get it from the dtime_start vs dtime_end .. up to you

This will give you a very generic (and therefore flexibile / scalable) model to work with users' subscriptions ... no duplications, all handled by FK/PK referential constraints, ... etc