Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It's not bad. However, I would suggest that you add two fields to <code>tbl_user</code>:</p> <pre><code>tbl_user ---------------- id name status_id activated_date expiry_date </code></pre> <p>Of course, <code>activated_date</code> holds the date they were first activated, while <code>expiry_date</code> holds the date when they will expire. You also need a procedure to update this <code>expiry_date</code>, whenever they buy a new card. This procedure should handle two cards with overlapping dates, so the user doesn't double-up payment for a particular period. For example:</p> <ul> <li><strong>Card 1</strong> - Sep 1 to Sep 30</li> <li><strong>Card 2</strong> - Sep 16 to Oct 15</li> </ul> <p>There are fifteen days of overlap there, so the user's <code>activated_date</code> should be <em>Sep 1</em>, while their <code>expiry_date</code> should be <em>Oct 30</em> (Oct 15 + 15 days).</p> <p>Considering this, I would change <code>tbl_activation_code</code>, as <code>expiry_date</code> becomes a bit misleading. Instead, create a column called <code>access_days</code> that will be used to calculate the user's <code>expiry_date</code>.</p> <p>Also, if you want to remember cards that were issued, even if not activated, then I would split <code>tbl_activation_code</code> into two tables:</p> <pre><code>tbl_activation_code ---------------- activation_code activation_code_type_id activation_code_status_id access_days tbl_activation ---------------- activation_code_id user_id activated_date </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow would you design this DB?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMike Hanson
    1. USMike Hanson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow would you design this DB?
      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. VTUpMod
    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. VTAcceptedByOriginator
    1. COtbl_user, of course, has a few other fields such as created_date, which is in fact is the activated_date you propose. as for the expiry_date in tbl_user, it looks as if we're "expiring" the user. probably not a good idea, huh? I feel the expiry_date should be for the activation_code, which would be calculated based on the activation_code_type_id and activated_date.
      singulars
      1. PO
      1. USNuku
    2. COActually, Mike speaks wisdom. Expiry_date doesn't mean the user expires, you can rename it as you wish. But besides benefits Mike has listed, this gives you one place lookup to determine if the user should be allowed in or not. This is also a way to write a scalable solution. Otherwise you would have to search through all the cards to see if he has activated any, find their max date and so on.
      singulars
      1. PO
      1. UShusayt
    3. COAs husayt says, you can rename the expiry_date field in tbl_user to activation_expiry_date. With regards to an expiry_date being associated with the card, it depends on how you view the cards. If they're considered to be "duration purchases", then expiry_date does not belong in tbl_activation_code. For example, what happens if the user buys and activates two 30-day cards on the same day. Do they get activated for 30 or 60 days? If, however, they "waste" their card by activating two in one day, then go ahead and leave the expire_date with the card (and anger your users <g>).
      singulars
      1. PO
      1. USMike Hanson
 

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