0

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.

NewDev90
  • 379
  • 2
  • 21
  • If you only want to store the original price, you might as well store it as Subscription.OriginalPrice (a la [SCD-3](https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_3:_add_new_attribute)). Or, more likely, you'll want to store the complete history of a subset of attributes in a history table. – Nickolay Jan 24 '21 at 13:02
  • It's not clear whether 2 rows with the same subscriptionid can have different price-currency pair. Do you mean you can change subscriptionid in 2 rows to have the same new value, leaving them with different old price-currency pairs? If you can, then there's no FD. PS It's unlikely this design arises from whatever information modeling method you're following. PS "Premature optimization is the root of all evil." PS Why do you need us to tell you what the problems with violating 3NF are? What is your problem? – philipxy Jan 24 '21 at 13:31
  • @philipxy the idea is that the subscriptionid in the UserSubscription table is used to reference the "original" subscription that the values arise from. So if subscriptionid = 5, change then we still have the values that the subscription used to hold (and that is relevant for the context of the user here which is what the user is required to pay for it). In terms of my problem, I tried to clearly state that I broke 3NF, I am just wondering IF this approach could ever be useful, or whether any implications will be bad no matter what. – NewDev90 Jan 24 '21 at 14:57
  • Please clarify via edits, not comments. PS You still haven't clarified "whether 2 rows with the same subscriptionid can have different price-currency pairs". Yes or no? From your descriptions (which are not clear about exactly what is getting changed when; an example would help), 2 rows can have (via update) the same subscriptionid rows with different (old) price-currency pairs; but if that can happen then there is no transitive FD. PS Putting information in a table that is available elseways (violating a NF or not) is a faq. – philipxy Jan 24 '21 at 18:31
  • It seems like the historical c-p pair is only stored in this table & current s does not determine it, so re this post's issue the design is fine. The problem with not asking about where you are stuck in a published design method is--what do you expect an answer to say? It has to rewrite a tutorial presentation of your method without you telling us what your method is or where you are stuck in it. There is not just one information modeling method or DB design method. Also, if you don't have a reason to do what you're doing following a method, why are you even doing it? (Rhetorical.) – philipxy Jan 24 '21 at 18:53
  • @philipxy I apologize for not being 100% clear in my question in that case. My idea was whether not following normalization methods can be necessary whenever I would need to introduce more complex logic in order to manage it in the implementation code. I am familiar with more than one way to resolve it without issues, and I can do that quiet easily. I was just wondering whether it was necessary. And as you point out, there will most definitely be cases where 2 point to the same id, with different price-currency pairs. I just want to point to which subscription it originally pointed to. – NewDev90 Jan 25 '21 at 06:16
  • Again: Please clarify via edits, not comments. PS "this transitive dependency" What "transitive dependency"? A FD is a a statement about 1 table. A FK is not a FD. Having 2 tables constrained in some way cannot be a "transitive FD". Whatever you think the problem is, you should not use "transitive FD" or "normalization" to describe it because you don't use those in ways that make sense. Also explain/justify how the problem is a problem. Also the design alternative descriptions & justifications are unclear. It's also not clear what the 1 clear specific researched non-duplicate question is. – philipxy Apr 13 '22 at 07:52

1 Answers1

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

Mr.P
  • 1,182
  • 3
  • 20
  • 44
  • Thanks for your input, I have thought about solving my issue in many ways, with such an example as you provide as well. My primary point is not how I can resolve it, but whether I need to resolve it at all. I am trying to identify why my model is an issue in relation to 3NF, when the example (in my opinion) is not a very unique one, but can apply to many different and similar problems. The idea is just to reduce the complexity of having to add more models to the subscription table each time something change (when it does often). – NewDev90 Jan 24 '21 at 14:59
  • 1
    I am still not quite sure about what you want to achieve or get from us here .... if I got that right now, you are asking whether or not the 3NF is dogmatic.. right? Well the thing is, Normal Forms are guidelines telling you how to create flexible, scalable, and long term sustainable models .. but the fact is, you don't have to necessarily stick with them .. there are several occations when you have to denormalize (and it's ok .. more than that - it's necessary) in order to improve the system/process performance .. it all depends on requirements – Mr.P Jan 24 '21 at 17:52
  • that is exactly what I was trying to aim towards thanks. I was been taught to abide to normalization no matter what, but I have found multiple occasions by now, where I would need to introduce unnecessary complexity into the system in order to do just that. – NewDev90 Jan 25 '21 at 05:54
  • just be sure your data are consistent at all times ... with denormalized models, it might be pain in the a$$ – Mr.P Jan 25 '21 at 06:11