CODEWITHSUNDEEP
mysqlsqlsql-serverdatabasedatabase-design
bdutta74
bdutta74

Reputation: 2860

How to model this & equivalent SQL -- User, Subscription, Periodical

Got three entities -

  1. User - Has username/password, contact information, billing information etc.
  2. Periodical - Has periodical_name, category, publisher_info, print_cycle, unit_price etc.
  3. Subscription - Has the user-id, periodical-id, subscription start/end date, status etc.

And following relationships -

s.t.,

My questions --

  1. Is this model description correct for the real-world relationships typically found ?

  2. Or, am I better off, collapsing Periodical into Subscription, especially if per Periodical information is not extensive and can be encoded into, say a delimiter separate text field (e.g. "PeriodicalName:Frequency:Publisher:UnitPrice") ?

  3. Can I say that via associativity User--Periodical relation is Many-to-Many ?

  4. Would appreciate if someone can show how I could put a constraint around either User or Subscription table (assuming that I don't collapse Subscription & Periodical), s.t. when a User needs to be removed for some reason, all associated Subscriptions are deleted too.

  5. Would like to keep a User record around for sometime (say a year), even after all Subscriptions have expired, so I guess I can assign NULL to the FK subscription_id in User table, right ? This is when no corresponding record exists in Subscription table.

Upvotes: 1

Views: 1080

Answers (2)

dkretz
dkretz

Reputation: 37645

  1. Yes.

  2. You are describing "denormalization" which may under rare circumstances be useful but it's a violation of relational design.

  3. yes.

  4. This type of constraint (1-to-1) is not implemented declaratively in most RDBMS products. Instead we have 1-to-zero-or-one. You can do it with triggers but it's tricky and subtle. Google for Object-Role Modeling which is a more comprehensive modeling technique that addresses stuff like this (and "either-or" and "at least 2" and many others.)

  5. That would be the standard way to handle it. You could also leave the subscription with an expiration date. But the 1-to-zero-or-one deficiency nicely facilitates your suggestion.

Upvotes: 2

Hogan
Hogan

Reputation: 70523

  1. You model is correct. I would implement this as 3 tables User, Periodical, Subscription.

  2. You should not collapse Periodical into Subscription. If a Periodical or some portion of the periodical is the same for all users it goes in that table. Anything that changes by subscription goes in the subscription table.

  3. Yes.

  4. I think this is a bad idea. You are talking about Business logic here. That should not be implemented in the database. Your requirements might change. You can require that all users (FKs) in the subscription table exist -- but you should not have the data tier remove subscriptions when a user is deleted -- instead it will block and say it can't remove because the FK is being used in the subscription table. (Your logic layer will need to delete all subscriptions before it can delete the user.)

  5. You don't need a FK in the user table for subscriptions. The subscription table contains the FK for both user and periodical -- it is what is called a join table. You would need a join when you want to retrieve both user data from the user table and the subscription information. Often you might not need this (for example you know the user id and you just want to list that user's subscription information).

Upvotes: 1

Related Questions