Need help in creating a back end support for a new project

Question

Post reply

Need help in creating a back end support for a new project

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
December 23, 2008 at 3:21 am

#96867

Some sample scenario:
Root(1)
Left(2.1)Right(2.2)
Left(3.1) Right(3.2) Left(3.3)Right(3.4)
And it grows
This is somewhat like a marketing strategy
If 2.1 or 2.2 buys some item for 50(currency) then they will get 50 points and the Root(their parent) will get 20 points.
Similarly if 3.1 or 3.2 buys some item for 50(currency) then they will get 50 points and their parent(2.1) will get 20 points and Root(2.1’s parent) will also get 20 points.
For this kind of strategy, please can I know how to design the table?
Create Table table1 (--can I know why # will be used here
UserID int PRIMARY KEY,
Name varchar(50)
)
Create Table table2 (
UserID int FOREIGN KEY REFERENCES table1(UserID),
ParentID int,
--One more column to track which side he is( left or right )
Points float
)
Will only 2 tables will be enough for this or do we need to create another table?
What about columns?Do we need any more columns?
I think we need to deal with TRIGGERS to update the values. Like, if 2.1 get 50 points then Root should be added with 20 points.
Please can I know how to proceed about it.
(some ideas)
Thanks
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

I feel you need only one User table. Columns - UserID,Name,ParentUserID.

Whats the logic behind Right & Left. Wont knowing the parent of the user suffice?

"Keep Trying"

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 2

Chirag (12/23/2008)
Hi
I feel you need only one User table. Columns - UserID,Name,ParentUserID.
Whats the logic behind Right & Left. Wont knowing the parent of the user suffice?

Ok I created the table

Create Table #market_san(

UserID int PRIMARY KEY,

Name varchar(50),

ParentID int,

Points float

)

Sample values

INSERT INTO #market_san(ID,Name,ParentID,Points)

SELECT '1','abc1','0','0', UNION ALL

SELECT '2','abc2','1','0', UNION ALL

SELECT '3','abc3','2','0', UNION ALL

SELECT '4','abc4','0','0', UNION ALL

SELECT '5','abc5','3','0', UNION ALL

SELECT '6','abc6','1','0', UNION ALL

SELECT '7','abc7','1','0', UNION ALL

SELECT '8','abc8','2','0', UNION ALL

SELECT '9','abc9','7','0'

or

insert into #market_san values(1,'abc1','','')

insert into #market_san values(2,'abc2','1','')

insert into #market_san values(3,'abc3','2','')

insert into #market_san values(4,'abc4','','')

insert into #market_san values(5,'abc5','3','')

insert into #market_san values(6,'abc6','1','')

insert into #market_san values(7,'abc7','1','')

insert into #market_san values(8,'abc8','2','')

insert into market_san values(9,'abc9','7','')

1. Suppose now the UserID 9 gets 50 points

2. Since the ParentID of UserID 9 is 7, UserID 7 gets 20 points

3. Again the ParentID of UserID 7 is 1 so UserID 1 must also get 20 points

How can we write a query/trigger to do this job?

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

You can use a CTE to find out the parentusers. Ex: Userid 9 -> UserID 7 -> UserID 1 and so on. You can then assign points to these parent users according to the business logic.

You can also use a WHILE loop instead of CTE to find the parentusers, but if the the number of records is not very high

use a CTE.

Do this inside a stored proc rather than a trigger as it will be easier to control the execution and debug in case of any errors.

"Keep Trying"

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

Table1

create table usertbl

(

userid int identity(100,1) primary key,

username varchar(50),

parentid int foreign key references usertbl(userid),

product varchar(50),

pValue decimal(7,2)

)

Table2

create table userscore

(

userid int foreign key references usertbl(userid),

score int

)

CREATE TRIGGER updating on usertbl for insert

as

begin

declare @parentuserid int

select @parentuserid=parentid from inserted

insert into userscore values(inserted.userid,inserted.pvalue)

while (@parentuserid<> NULL)

begin

update userscore set score=(score+inserted.pvalue) where userid=@parentuserid

select @parentuserid=parentid from usertbl where userid=@parentuserid

end

I think the above TRIGGER is in Oracle syntax.

Can I have this code in MS SQL Syntax?

OR

Can I have the stored procedure that does the same job?

Thanks

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

Hi

Iam afraid you will have to write your own storedproc/ trigger. I cant do that for you. If you know oracle then doing it in SQL should not be a big deal. Anyway the code that you have posted looks good. So give it a try.

"Keep Trying"

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 6

Chirag (12/23/2008)
Hi
Iam afraid you will have to write your own storedproc/ trigger. I cant do that for you. If you know oracle then doing it in SQL should not be a big deal. Anyway the code that you have posted looks good. So give it a try.

Yes I am trying

Thanks

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

Santhosh (12/23/2008)
Chirag (12/23/2008)
Hi
Iam afraid you will have to write your own storedproc/ trigger. I cant do that for you. If you know oracle then doing it in SQL should not be a big deal. Anyway the code that you have posted looks good. So give it a try.
Yes I am trying
Thanks

Great.

"Keep Trying"