Databases
StackOverflow2013
Postsdbo
POHow to model this & equivalent SQL -- User, Subscription, Periodical
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to model this & equivalent SQL -- User, Subscription, Periodical
    text
    copied!<p>Got three <strong>entities</strong> -</p> <ol> <li>User - Has username/password, contact information, billing information etc.</li> <li>Periodical - Has periodical_name, category, publisher_info, print_cycle, unit_price etc.</li> <li>Subscription - Has the user-id, periodical-id, subscription start/end date, status etc.</li> </ol> <p>And following <strong>relationships</strong> -</p> <ul> <li>User can have Subscription for multiple Periodicals.</li> <li>Subscription belongs-to a User, and has-a Periodical associated.</li> <li>Periodical has-many Subscriptions associated with it.</li> </ul> <p>s.t.,</p> <ul> <li>User--Subscription relation is One-to-Many,</li> <li>Periodical--Subscription relation is One-to-Many,</li> </ul> <p>My questions --</p> <ol> <li><p>Is this model description correct for the real-world relationships typically found ?</p></li> <li><p>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") ?</p></li> <li><p>Can I say that via <em>associativity</em> User--Periodical relation is Many-to-Many ?</p></li> <li><p>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 &amp; Periodical), s.t. when a User needs to be removed for some reason, all associated Subscriptions are deleted too. </p></li> <li><p>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.</p></li> </ol>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload