• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

sql database schema

i have a table services has this field serviceid (p.k) servicename
and i have table subscriber with field subscriberid  (p.k) subscibername
and table subscription with field subscriptionid (p.k) serviceid (f.k) subscriberid (f.k) startdate enddate


the user can subscriber to any service in any day of the month and subscription period is one month !! user A subscribe to service A start date 5/9/2012 end date 5/10/2012
user B subscribe to service B start date 10/9/2012 end date 10/10/2012

so when user subscribe to a service he will automatically receive notification msg to his email

i want to add an automatically subscription... but i should add new field in subscription or what??
because in table of subscription i want to store  each month who of users are subscribe to my service at last month ...

please if some one have another idea about subscription schema
0
AFIF JABADO
Asked:
AFIF JABADO
  • 5
  • 4
1 Solution
 
lwadwellCommented:
>> i want to add an automatically subscription
Are you saying you want to add the capability of allowing subscriptions to not automatically end after a month?

If you want open ended subscriptions ... I would just leave the end date in subscriptionid as null or a long way in the future date.
0
 
AFIF JABADOAuthor Commented:
i want to view all subscription start and end date before make statistic..
and want automatically add new row in table of subscription  for the next month
and it will be active but when user send notification to email system will read it bcz it will be stored in other table ! and  it will unsubscribe but if he doesn't send anything he will automatic subscribe for the next month ?

i need to change in database schema

bcz this service is related to short code mobile service
where i have a table contain what msg should be sent to mobile user when he subscribe to a specific service name !


so  ????????
0
 
lwadwellCommented:
>> i want to view all subscription start and end date before make statistic
you can

>> and want automatically add new row in table of subscription  for the next month
why?

>> but when user send notification to email system will read it bcz it will be stored in other table
to un-subscribe ... update the row to have a different end date
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AFIF JABADOAuthor Commented:
mobile users sent sms to a short code to subscribe to a service name each service has start and enddate

the startdate will be the date that my system receive the sms !
than he will be charged for this service  for 1 month

i want to automatically subscribe the user again for the next month if i didn't receive any sms from the mobile user to cancel the subscription !!!!!!! <-- how ??

i want to make a better schema  for this stuff
0
 
AFIF JABADOAuthor Commented:
it\s not good to update the table f enddate bcz user can subscribe this month for example and next month cancel the subscription and the next monh will subscribe !!
0
 
lwadwellCommented:
>> i want to automatically subscribe the user again for the next month if i didn't receive any sms from the mobile user to cancel the subscription
For that to occur ... you would need to run a (batch) process on the anniversary of the subscription to add the new row per subscription.  For this to work you will need to keep a flag somewhere that the subscription has not been cancelled.  You cannot put this in subscriber as they may have multiple services.  If you put it in subscription - the batch process needs to ensure that the correct row is used when creating new rows.
I am proposing a solution that does not require the database to be updated every day by a process like this.
If the user cancels - an update to the subscription table is required either way - my suggestion is to update the date, yours is update the flag.

>> i want to make a better schema  for this stuff
I am offering you one ... you are rejecting it.

>> it\s not good to update the table f enddate bcz user can subscribe this month for example and next month cancel the subscription and the next monh will subscribe !!
Can this not happen anyway?  You already need to add logic to ensure they do not subscribe twice or more.  If they choose to come back ... why would you stop them?
0
 
AFIF JABADOAuthor Commented:
sry i have told u my schema ? i should modify it ??? or it\s good ?
0
 
lwadwellCommented:
Sorry ... been very sick the last few days.
No your schema looked fine, based on my limited knowledge ... you just need to design its use for all scenarios.
0
 
AFIF JABADOAuthor Commented:
thank you :) hope you are fine now..
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now