Need help do design the table for this requirement
-
I am developing a web application and using mssql as backend. This is my requirement where i need to create tables so that it will meet my requirment. I am looking for a efficient method. I have a usertable where user will get registered Free.They can submit their articles for monthly payment scheme.month calculation is based on the day they have submitted their article.So if they pay for that month, then thier article will be displayed in the web for that whole month and for the next month they have to pay again to intialize the process again. Usertable articletable accountTable usercode articlecode AccountCode since user have to pay every month the relation between article table and account table is 1 ->many, but the problme here is not about inserting the data into the account table but generating it after the end of the month. i need a process to generate where it checks the users date and calculate the month and based on that month have to create a unpaid status. Thanks and regards vimal Help in need is the help indeed
-
I am developing a web application and using mssql as backend. This is my requirement where i need to create tables so that it will meet my requirment. I am looking for a efficient method. I have a usertable where user will get registered Free.They can submit their articles for monthly payment scheme.month calculation is based on the day they have submitted their article.So if they pay for that month, then thier article will be displayed in the web for that whole month and for the next month they have to pay again to intialize the process again. Usertable articletable accountTable usercode articlecode AccountCode since user have to pay every month the relation between article table and account table is 1 ->many, but the problme here is not about inserting the data into the account table but generating it after the end of the month. i need a process to generate where it checks the users date and calculate the month and based on that month have to create a unpaid status. Thanks and regards vimal Help in need is the help indeed
So, let's get this straight. 1 user can have many articles. 1 article can have many accounts (one for each month) The bill payment date is based on the day of the month of when the article was first submitted. What happens if the user submits two articles, one on the 5th of some month and one on the 20th of some month? According to what you have above they receive two bills, one for each article? Is this right? percyvimal wrote: i need a process to generate where it checks the users date and calculate the month and based on that month have to create a unpaid status. What do you mean by this? Are you looking for a process to set some flag on the article table to say whether the article has been paid for or not? Tip: Can I suggest that you use positive names for flags, because that way, when you come to use the value of flags it becomes less confusing - e.g.
if(!IsUnpaid)is harder to read thanif (IsPaid)
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
So, let's get this straight. 1 user can have many articles. 1 article can have many accounts (one for each month) The bill payment date is based on the day of the month of when the article was first submitted. What happens if the user submits two articles, one on the 5th of some month and one on the 20th of some month? According to what you have above they receive two bills, one for each article? Is this right? percyvimal wrote: i need a process to generate where it checks the users date and calculate the month and based on that month have to create a unpaid status. What do you mean by this? Are you looking for a process to set some flag on the article table to say whether the article has been paid for or not? Tip: Can I suggest that you use positive names for flags, because that way, when you come to use the value of flags it becomes less confusing - e.g.
if(!IsUnpaid)is harder to read thanif (IsPaid)
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
Thank you mr Colin Angus Mackay I hope u have understood my requirement. 1. It is a continous process, where each day new user will come and they will register and they will upload articles. 2. So assume yourself as a user. 3.now u are submitting an article and paying on say 10/10/2004 usercode articlecode acccountcode paymentdate expirydate amount Status 10--------23------------123--------10/10/2004----10/11/2004----50$----Paid This is not a table structure but just i just put to explain. 4.now i check ur status and see that as paid and so i will show ur article in the web. 5.But it should be a continous process where after end of expiry date i need to create a new status as unpaid so that only after paying for next month term i can show that article 6.I need to keep track of all his account details also and hence i cant overwrite on a single record. 7. I hope u have understood my requirment Usertable article table and accounttable these are the tables i think i need to create but i dont know how i create the structure and relationship between these tables and what are the new tables i need to create to susport the same i need your help in designing the tables so that i can manage this process easily and efficetively and ur help in how to go about this process also really welcome thanks and regards vimal Help in need is the help indeed
-
Thank you mr Colin Angus Mackay I hope u have understood my requirement. 1. It is a continous process, where each day new user will come and they will register and they will upload articles. 2. So assume yourself as a user. 3.now u are submitting an article and paying on say 10/10/2004 usercode articlecode acccountcode paymentdate expirydate amount Status 10--------23------------123--------10/10/2004----10/11/2004----50$----Paid This is not a table structure but just i just put to explain. 4.now i check ur status and see that as paid and so i will show ur article in the web. 5.But it should be a continous process where after end of expiry date i need to create a new status as unpaid so that only after paying for next month term i can show that article 6.I need to keep track of all his account details also and hence i cant overwrite on a single record. 7. I hope u have understood my requirment Usertable article table and accounttable these are the tables i think i need to create but i dont know how i create the structure and relationship between these tables and what are the new tables i need to create to susport the same i need your help in designing the tables so that i can manage this process easily and efficetively and ur help in how to go about this process also really welcome thanks and regards vimal Help in need is the help indeed
The following function will return the ExpiryDate for any article for which the article code is known.
CREATE FUNCTION dbo.ExpiryDate(@ArticleCode int)
RETURNS datetime
AS
BEGIN
DECLARE @ExpiryDate datetimeSELECT @ExpiryDate = MAX(ExpiryDate) FROM Accounts WHERE ArticleCode = @ArticleCode RETURN @ExpiryDateEND
When you are creating your article table you can add a calculated column, so that its value is created anytime it is asked for. The table stubs needed to support this, and the other information you've given me are:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Accounts_Articles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Accounts] DROP CONSTRAINT FK_Accounts_Articles
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Articles_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Articles] DROP CONSTRAINT FK_Articles_Users
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExpiryDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ExpiryDate]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Accounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Accounts]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Articles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Articles]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.ExpiryDate(@ArticleCode int)
RETURNS datetime
AS
BEGIN
DECLARE @ExpiryDate datetimeSELECT @ExpiryDate = MAX(ExpiryDate) FROM Accounts WHERE ArticleCode = @ArticleCode RETURN @ExpiryDateEND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOCREATE TABLE [dbo].[Accounts] (
[AccountCode] [int] NOT NULL ,
[ArticleCode] [int] NOT NULL ,
[Amount] [money] NOT NULL ,
[PaymentDate] [datetime] NOT NULL ,
[ExpiryDate] [datetime] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Articles] (
[ArticleCode] [int] NOT NULL ,
[UserCode] [int] NOT NULL ,
[IsPaid] AS (case when (getdate() < [dbo].[ExpiryDate]([ArticleCode])) then 1 else 0 end)
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Users] (
[UserCode] [int] NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[Accounts] ADD
CONSTRAINT [PK_ -
The following function will return the ExpiryDate for any article for which the article code is known.
CREATE FUNCTION dbo.ExpiryDate(@ArticleCode int)
RETURNS datetime
AS
BEGIN
DECLARE @ExpiryDate datetimeSELECT @ExpiryDate = MAX(ExpiryDate) FROM Accounts WHERE ArticleCode = @ArticleCode RETURN @ExpiryDateEND
When you are creating your article table you can add a calculated column, so that its value is created anytime it is asked for. The table stubs needed to support this, and the other information you've given me are:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Accounts_Articles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Accounts] DROP CONSTRAINT FK_Accounts_Articles
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Articles_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Articles] DROP CONSTRAINT FK_Articles_Users
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExpiryDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ExpiryDate]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Accounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Accounts]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Articles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Articles]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.ExpiryDate(@ArticleCode int)
RETURNS datetime
AS
BEGIN
DECLARE @ExpiryDate datetimeSELECT @ExpiryDate = MAX(ExpiryDate) FROM Accounts WHERE ArticleCode = @ArticleCode RETURN @ExpiryDateEND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOCREATE TABLE [dbo].[Accounts] (
[AccountCode] [int] NOT NULL ,
[ArticleCode] [int] NOT NULL ,
[Amount] [money] NOT NULL ,
[PaymentDate] [datetime] NOT NULL ,
[ExpiryDate] [datetime] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Articles] (
[ArticleCode] [int] NOT NULL ,
[UserCode] [int] NOT NULL ,
[IsPaid] AS (case when (getdate() < [dbo].[ExpiryDate]([ArticleCode])) then 1 else 0 end)
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Users] (
[UserCode] [int] NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[Accounts] ADD
CONSTRAINT [PK_Thank you very much, Your idea abour expiry date really gave me a way to identify a solution for this problem We will have a menu for admin where on clicking that one , i select all the articles whose status is active i will have the expirydate field for every articles. I check if expirydate > Currentdate then 1.create a new accountcode and ask the user to pay against that accountcode 2.make the expirtydate.month= expirydate.moth+1(increments the month) 3.make the status of the article inactive 4.when user pay the amount for that account make the article active. else no problem with that record for it is to be active end if I need your help to find out any problem associated with this method Thanks a lot mate for your help with regards vimal Help in need is the help indeed
-
Thank you very much, Your idea abour expiry date really gave me a way to identify a solution for this problem We will have a menu for admin where on clicking that one , i select all the articles whose status is active i will have the expirydate field for every articles. I check if expirydate > Currentdate then 1.create a new accountcode and ask the user to pay against that accountcode 2.make the expirtydate.month= expirydate.moth+1(increments the month) 3.make the status of the article inactive 4.when user pay the amount for that account make the article active. else no problem with that record for it is to be active end if I need your help to find out any problem associated with this method Thanks a lot mate for your help with regards vimal Help in need is the help indeed
percyvimal wrote: I check if expirydate > Currentdate then 1.create a new accountcode and ask the user to pay against that accountcode So, meanwhile the article goes off line, because it has exprired. I would suggest that you contact the user, say, two weeks in advance of an expiry. And that you have an additional flag in the new account record to say whether it is paid up or not, if not it isn't used in the calculation of the expiry date, if it is paid up the record will reflect that and the expiry date will reflect that. It really sounds like you need to work on your business model, or converting your business model into a logical model for your system. If you are not familiar with Use Cases, or don't want to go through the formality of them, can I suggest that you create short stories (about a paragraph each) of what various users of the system will want to do. In each make sure you write in exactly what information is needed and the actions that need to be performed are to achieve the goal. From that you will end up of a list of bits of information, the business rules and the relationships between them. For instance: A user receives an email telling them their account is going to expire in two weeks time. The user logs on to the system, goes to their account page and renews their subscription. So, you've got a user, an email address, an account, an expiry date, log-on information, details about a subscription. A user has an email address. A user has a username and password (the log-on information) An account is a synonym for subscription? or not? And so on. It is also important to be careful with the terminology. For instance, the relationships between user, account and subscription are (to me): one user ==> 1..n accounts ==> 1..n subscriptions. So, if you see in your user stories some ambiguity in the names of things try and straighten it out. The idea of having a different account each month is confusing, IMO. Does this help?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.