How should my database like
-
:confused:I am using sql 2000. I have a scenario is 1 user can buy many or 0 'package'. and 1 'package' can have 0 or many users. If the user didnt buy the 'package' then he/she will not allow to use the package. How should I create my database. How is my database look like.
-
:confused:I am using sql 2000. I have a scenario is 1 user can buy many or 0 'package'. and 1 'package' can have 0 or many users. If the user didnt buy the 'package' then he/she will not allow to use the package. How should I create my database. How is my database look like.
In other words a many-to-many join Here are the key columns in use and their tables User: UserID Package: PackageID UserPackage: UserID, PackageID The UserPackage table is the intermediate table in the many-to-many join. It has a compound key consisting of both sets of primary keys. When you need to join a user to a package you put a row in the UserPackage table. Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
In other words a many-to-many join Here are the key columns in use and their tables User: UserID Package: PackageID UserPackage: UserID, PackageID The UserPackage table is the intermediate table in the many-to-many join. It has a compound key consisting of both sets of primary keys. When you need to join a user to a package you put a row in the UserPackage table. Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
I am actually creating my project in .net. ok now i have my sql command as like this SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX for XXXXXXXX what should I put? my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.
-
I am actually creating my project in .net. ok now i have my sql command as like this SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX for XXXXXXXX what should I put? my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.
LovelyHelp wrote:
I am actually creating my project in .net
I never doubted it.
LovelyHelp wrote:
SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX
Using SELECT * is a bad idea - if your data model changes the application that is built on top may break becuase of columns being returned that it did not expect, or columns that no longer exist. You should always list the columns you want - This also has the potential benefit of reducing network bandwidth necessary to transmit columns that will get discarded on reaching the application. Also, when you join two or more tables together you will often get situations where there are columns with the same name. If you list columns explicitly you get the opportunity to rename columns. The other point about this code is that it looks like it is susceptable to a SQL Injection Attack and you don't want your database to be compromised. See here on how to prevent SQL Injection Attacks[^]
LovelyHelp wrote:
for XXXXXXXX what should I put?my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.
I don't know what to put for the XXXXX because it is your application and you haven't told me how it handles products. I'll assume that your application knows the productID (because that's what the code looks like it should know) Also, from your code it looks like the user name is the key on the user table. (I would recommend keying on a number as it is more efficient than keying on a string) Anyway your query is very simple:
SELECT COUNT(*) FROM t_userPackage WHERE userID = @userName AND packageID = @packageID
I'm assuming that if the user pays for a package that a join beween the user and package tables will exist. That being the case, the code above will return 0 if the user has not paid for the package. It will return 1 if the user has paid for the package. I've also used parameters in the query as a part defence against SQL Injection Att
-
LovelyHelp wrote:
I am actually creating my project in .net
I never doubted it.
LovelyHelp wrote:
SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX
Using SELECT * is a bad idea - if your data model changes the application that is built on top may break becuase of columns being returned that it did not expect, or columns that no longer exist. You should always list the columns you want - This also has the potential benefit of reducing network bandwidth necessary to transmit columns that will get discarded on reaching the application. Also, when you join two or more tables together you will often get situations where there are columns with the same name. If you list columns explicitly you get the opportunity to rename columns. The other point about this code is that it looks like it is susceptable to a SQL Injection Attack and you don't want your database to be compromised. See here on how to prevent SQL Injection Attacks[^]
LovelyHelp wrote:
for XXXXXXXX what should I put?my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.
I don't know what to put for the XXXXX because it is your application and you haven't told me how it handles products. I'll assume that your application knows the productID (because that's what the code looks like it should know) Also, from your code it looks like the user name is the key on the user table. (I would recommend keying on a number as it is more efficient than keying on a string) Anyway your query is very simple:
SELECT COUNT(*) FROM t_userPackage WHERE userID = @userName AND packageID = @packageID
I'm assuming that if the user pays for a package that a join beween the user and package tables will exist. That being the case, the code above will return 0 if the user has not paid for the package. It will return 1 if the user has paid for the package. I've also used parameters in the query as a part defence against SQL Injection Att
I am very new to asp.net and database. Thanks alots and I have learn from you. actually for my function it have something like when user click on the link of particular package then it will run the sql command to check whether have the user pay for the particular package. I am trying to use dataList to build a set of my list so what will be my XXXXXX? is this two line of code cmd.Parameters.Add("@userName", User.Identity.Name); cmd.Parameters.Add("@productID", productID); same as cmdSelect.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = User.Identity.Name cmdSelect.Parameters.Add("@productID", SqlDbType.Int, 4).Value = XXXXXX I think you are using c right. anyway your technique is new to me.
-
LovelyHelp wrote:
I am actually creating my project in .net
I never doubted it.
LovelyHelp wrote:
SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX
Using SELECT * is a bad idea - if your data model changes the application that is built on top may break becuase of columns being returned that it did not expect, or columns that no longer exist. You should always list the columns you want - This also has the potential benefit of reducing network bandwidth necessary to transmit columns that will get discarded on reaching the application. Also, when you join two or more tables together you will often get situations where there are columns with the same name. If you list columns explicitly you get the opportunity to rename columns. The other point about this code is that it looks like it is susceptable to a SQL Injection Attack and you don't want your database to be compromised. See here on how to prevent SQL Injection Attacks[^]
LovelyHelp wrote:
for XXXXXXXX what should I put?my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.
I don't know what to put for the XXXXX because it is your application and you haven't told me how it handles products. I'll assume that your application knows the productID (because that's what the code looks like it should know) Also, from your code it looks like the user name is the key on the user table. (I would recommend keying on a number as it is more efficient than keying on a string) Anyway your query is very simple:
SELECT COUNT(*) FROM t_userPackage WHERE userID = @userName AND packageID = @packageID
I'm assuming that if the user pays for a package that a join beween the user and package tables will exist. That being the case, the code above will return 0 if the user has not paid for the package. It will return 1 if the user has paid for the package. I've also used parameters in the query as a part defence against SQL Injection Att
I have some doubted. why are u using SELECT count(*)...? Does count mean counting?. and userID = @userName AND packageID = @packageID userID is int and can it compare with @username which it is varchar? in my database table. I have create something like t_package: packageID (pk), packageName t_user: userID (pk), userName, password, gender.... t_userPackage: userID, packageID
-
I am very new to asp.net and database. Thanks alots and I have learn from you. actually for my function it have something like when user click on the link of particular package then it will run the sql command to check whether have the user pay for the particular package. I am trying to use dataList to build a set of my list so what will be my XXXXXX? is this two line of code cmd.Parameters.Add("@userName", User.Identity.Name); cmd.Parameters.Add("@productID", productID); same as cmdSelect.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = User.Identity.Name cmdSelect.Parameters.Add("@productID", SqlDbType.Int, 4).Value = XXXXXX I think you are using c right. anyway your technique is new to me.
LovelyHelp wrote:
so what will be my XXXXXX
Whatever the value of your productID is going to be.
LovelyHelp wrote:
I think you are using c right.
He is using C# which is in the same family of languages as C/C++. Many similarities as well as differences. Paul
-
I am very new to asp.net and database. Thanks alots and I have learn from you. actually for my function it have something like when user click on the link of particular package then it will run the sql command to check whether have the user pay for the particular package. I am trying to use dataList to build a set of my list so what will be my XXXXXX? is this two line of code cmd.Parameters.Add("@userName", User.Identity.Name); cmd.Parameters.Add("@productID", productID); same as cmdSelect.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = User.Identity.Name cmdSelect.Parameters.Add("@productID", SqlDbType.Int, 4).Value = XXXXXX I think you are using c right. anyway your technique is new to me.
LovelyHelp wrote:
is this two line of code cmd.Parameters.Add("@userName", User.Identity.Name); cmd.Parameters.Add("@productID", productID); same as cmdSelect.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = User.Identity.Name cmdSelect.Parameters.Add("@productID", SqlDbType.Int, 4).Value = XXXXXX
Your two lines of code are slightly more efficient because the application does not need to do a roundtrip to the database to get the types of the parameter because you supply them. The code I put was for simplicity. Also, I don't see your code because you have not escaped the < symbols - You need to replace them with < so they show up.
LovelyHelp wrote:
I think you are using c right
I'm using C#
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
I have some doubted. why are u using SELECT count(*)...? Does count mean counting?. and userID = @userName AND packageID = @packageID userID is int and can it compare with @username which it is varchar? in my database table. I have create something like t_package: packageID (pk), packageName t_user: userID (pk), userName, password, gender.... t_userPackage: userID, packageID
LovelyHelp wrote:
Does count mean counting?
Yes, I want the number of links between the tables for a secific package and user combination.
LovelyHelp wrote:
userID is int and can it compare with @username which it is varchar?
Right - We are finally beginning to get somewhere with what the data model is. Assumptions: The application knows about user names and package names and nothing about IDs.
SELECT COUNT(*)
FROM t_userPackage AS up
INNER JOIN t_user AS u ON up.userID = u.userID
INNER JOIN t_package AS p ON up.packageID = p.packageID
WHERE u.userName = @userName
AND p.packageName = @packageNameFrom the .NET application, add parameters for @userName and @packageName with information in the application. The result from the query is 0 (there is NO association between the user and package - assumming this means they have not paid for it) or 1 (there IS an associtation between the user and package - assuming this means they have paid for it) If an association can exist between a user and package with the user NOT paying for the package then you need to explain that so the SQL can be modified to get that result. Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
LovelyHelp wrote:
is this two line of code cmd.Parameters.Add("@userName", User.Identity.Name); cmd.Parameters.Add("@productID", productID); same as cmdSelect.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = User.Identity.Name cmdSelect.Parameters.Add("@productID", SqlDbType.Int, 4).Value = XXXXXX
Your two lines of code are slightly more efficient because the application does not need to do a roundtrip to the database to get the types of the parameter because you supply them. The code I put was for simplicity. Also, I don't see your code because you have not escaped the < symbols - You need to replace them with < so they show up.
LovelyHelp wrote:
I think you are using c right
I'm using C#
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
oh sorry.. my code is like but i think asp:buttonlink is not allow inside itemtemplate
-
LovelyHelp wrote:
Does count mean counting?
Yes, I want the number of links between the tables for a secific package and user combination.
LovelyHelp wrote:
userID is int and can it compare with @username which it is varchar?
Right - We are finally beginning to get somewhere with what the data model is. Assumptions: The application knows about user names and package names and nothing about IDs.
SELECT COUNT(*)
FROM t_userPackage AS up
INNER JOIN t_user AS u ON up.userID = u.userID
INNER JOIN t_package AS p ON up.packageID = p.packageID
WHERE u.userName = @userName
AND p.packageName = @packageNameFrom the .NET application, add parameters for @userName and @packageName with information in the application. The result from the query is 0 (there is NO association between the user and package - assumming this means they have not paid for it) or 1 (there IS an associtation between the user and package - assuming this means they have paid for it) If an association can exist between a user and package with the user NOT paying for the package then you need to explain that so the SQL can be modified to get that result. Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
OH many to many is such a complicated relationship for me. maybe should we start with the UI first. To display my item, should I use datalist or datagrid? I need to show something like: Tutorial's name Tutorial description but is optional 2 button or maybe link. Where 1st link will be proceed to tutorial if user already pay for it and another link will be purchasing link where it purchase the tutorial. so there is different tutorial. so when example like i have paid for tutorial1 and not paid for tutorial2 then when i click on link 'proceed' for tutorial1 then I will automatically go to tutorial1's pages if i click on tutorial2's 'proceed' button then it will redirect to purchasing pages.