Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Question Review</strong></p> <p>As @leanne said, you're modeling a <code>Subscription</code> whose specializations are, say, <code>MonthlySubscription</code> and <code>ComplimentarySubscription</code> (to give them a name for this answer).</p> <p>You know that a subscription can expire:</p> <ul> <li>For a <code>MonthlySubscription</code>, that happens when a user didn't pay the current month's subscription</li> <li>For a <code>ComplimentarySubscription</code>, the expiration date is assigned when it’s assigned to the user</li> </ul> <p>As you can see an <code>ExpirationDate</code> is an essential attribute of any <code>Subscription</code>, but the way you store it is different in each case. If the first case you'll have to calculate it based on the last event, in the latter you can retrieve it directly.</p> <p><strong>Dealing with Inheritance in the Data Base</strong></p> <p>So, to map this sample model to a database schema, you could go with the <a href="http://martinfowler.com/eaaCatalog/classTableInheritance.html" rel="noreferrer">Class Table Inheritance</a> pattern described in Martin Fowler's <a href="http://rads.stackoverflow.com/amzn/click/0321127420" rel="noreferrer">Patterns of Enterprise Application Architecture</a> book. Here is its intent:</p> <blockquote> <p>"Represents an inheritance hierarchy of classes with one table for each class".</p> </blockquote> <p>Basically you'll have a table with the attributes shared in common between the classes, and you'll be storing attributes specific to each class in a separate table.</p> <p>Keeping this in mind, let's review the options you proposed:</p> <blockquote> <ul> <li>Have another table <code>complimentary_subscription</code> with the user ID as the foreign key?</li> </ul> </blockquote> <p>Having a separate table for storing <code>ComplimentarySubscription</code> specific details sound good, but if you don't relate this one with the <code>subscription</code> table, you can end up with an user that has both a <code>MonthlySubscription</code> and a <code>ComplimentarySubscription</code>. Its foreign key should point to the <code>subscription</code> table, which is the one that tells you if a user has a subscription or not (and you'll have to enforce up to one subscription per user).</p> <blockquote> <ul> <li>Record a special "subscription" for them in <code>subscription</code>?</li> </ul> </blockquote> <p>Yes, you'll have to record that a user has a subscription either monthly or complimentary. But if you're thinking something like recording a special subscription whose amount is zero, you’re looking for a solution that matches your current design instead of searching for the right model for it (it might and it might be not).</p> <blockquote> <ul> <li>Or add another column to their user row for columns like <code>is_complimentary</code> and <code>complimentary_expires_date</code>?</li> </ul> </blockquote> <p>Personally I don't like this one because you're putting information where it doesn't belong. Taking that into account, where you will be storing the expiration date for complimentary subscriptions (remember that for monthly ones you are calculating it, and not storing the expiration date)? It seems that all that information is crying for its own "home". Also, if later you need to add a new type of subscription that table will begin to clutter.</p> <blockquote> <ul> <li>Add a more general <code>expires</code> column to the user row?</li> </ul> </blockquote> <p>If you do this, you'll have to deal with data synchronization each time the <code>subscription_event</code> gets changed (in the case of a monthly subscription). Generally I try to avoid this data-duplication situation.</p> <p><strong>Sample Solution</strong></p> <p>What I would do to favor extensibility when adding new types of subscription is to have the <code>subscription</code> table to store shared details between <code>MonthlySubscripton</code> and <code>ComplimentarySubscription</code>, adding a <code>type</code> column key that'll let you differentiate which kind of subscription a row is related to.</p> <p>Then, store details specific to each subscription type in its own table, referencing the parent <code>subscription</code> row.</p> <p>For retrieving data, you'll need an object in charge of instantiating the right type of <code>Subscription</code> given the <code>type</code> column value for a <code>subscription</code> row.</p> <p>You can take a look at the pattern in the "Patterns of Enterprise Application Architecture" book for further assistance on how to define the <code>type</code> column values, how to use a mapper object to do the <code>Subscription</code> instantiation and so on.</p> <hr> <p><strong>01/03/2012 Update: Alternatives for defining and handling the <code>type</code> column</strong></p> <p>Here's an update to clarify the following question posted by @enoinoc in the comments:</p> <blockquote> <p>Specifically for the suggested <code>type</code> column, could this be a foreign key pointing to a <code>Plans</code> table which describes different types of subscriptions, such as how many months before they expire without payment. Does that sound logical?</p> </blockquote> <p>It's ok to have that information in the <code>Plans</code> table, as long it´s not static information that doesn´t need to be edited. If that's the case, don't over-generalize your solution and put that knowledge in the corresponding <code>Subscription</code> subclass.</p> <p>About the foreign key approach, I can think of some drawbacks going that way:</p> <ul> <li>Remember that your goal is to know wich subclass of <code>Subscription</code> to use for each row in the <code>Plans</code> table. If all you got is a foreign key value (say, an integer) you'll have to write code to map that value with the class to use. That means extra work for you :)</li> <li>If you have to do unnecesary extra work, it's likely that mainteinance will be a pain: each time you add a new plan, you'll have to remember hardcoding it's foreign key value in the mapping code.</li> <li>Foreign keys might change in case of improper database export/import operations. If that happens, your mapping code will no longer work and you'll have to deploy your software again (or, at least, the part that changed).</li> </ul> <p><strong>Proposed Solution</strong></p> <p>What I'd do is to put into the <code>type</code> column in the <code>Plans</code> table. That column will hold the name of the class that knows how to build the right <code>Subscription</code> from a particular row.</p> <p>But: why do we need an object to build each type of <code>Subscription</code>? Because you'll be using information from different tables (<code>subscription_event</code> and <code>complimentary_subscription</code>) for building each type of object, and it´s always good to isolate and encapsulate that behavior.</p> <p>Let's see how the <code>Plans</code> table might look like:</p> <p>-- Plans Table --</p> <p>Id | Name | Type | Other Columns...</p> <p>1 | Monthly | <code>MonthlySubscriptionMapper</code> |</p> <p>2 | Complimentary | <code>ComplimentarySubscriptionMapper</code> |</p> <p>Each <code>SubscriptionMapper</code> can define a method <code>Subscription MapFrom(Row aRow)</code> that takes a row from the database and gives you the right instance of the <code>Subscription</code> subclass (<code>MonthlySubscription</code> or <code>ComplimentarySubscription</code> in the example).</p> <p>Finally to get an instance of the mapper specified in the <code>type</code> column (without using a nasty <code>if</code> or <code>case</code> statements) you can take the class name from the column's value and, by using reflection, create an instance of that class.</p>
    singulars
    1. This table or related slice is empty.
    1. POSubscription management logic
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USnick2083
    1. USnick2083
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSubscription management logic
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    1. COThanks for your comments, as it helps me understand the fundamental design decisions behind the suggested sample solution. Specifically for the suggested `type` column, could this be a foreign key pointing to a `Plans` table which describes different types of subscriptions, such as how many months before they expire without payment. Does that sound logical? I hope this doesn't show complete ignorance to what you described for class table inheritance.
      singulars
      1. PO
      1. USeoinoc
    2. COHi @eoinoc! Not at all, it´s a good question and I'm very happy to help. I've updated my post to answer that one too, check it out.
      singulars
      1. PO
      1. USnick2083
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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