How to Saving Multiple ID in One Column

Dear Sir,
I need one Table Design information.
My requirement is:
I have some products (say 10 products) in my master table. Each product has
Product ID (PK).
ID Code Desc
--
1 A1 AAAA
2 B1 BBBBBB
etc. etc...
I have one interface where the user will select the products that he is
interestered with. Mean One user may select product ID 1, 3, 5 and other may
select 2, 7.
I have to save the product info selected by that user in one Column of the
user table.
(I have to save the selected product in one column only)
So What Type will be for that Column and how do i implement logic to save
all selected product so that it will be easy to save and extract the values
also.
I think I can use some binary kind of valu for each product and save the
selected binary combination or similar logic to store and retrieve.
Please suggest.
Thanks
PrabhatPrabhat
Use RDBMS. Split the User table into produtsSeclected and use usertable for
storing base information only. then you will have three tables
Products where product id is primary
users where users id is primary
and Junction Table : Products selected with foreign keys of productid and
userid. thus participating in many to many relationship.
Regards
R.D
"Prabhat" wrote:

> Dear Sir,
> I need one Table Design information.
> My requirement is:
> I have some products (say 10 products) in my master table. Each product ha
s
> Product ID (PK).
> ID Code Desc
> --
> 1 A1 AAAA
> 2 B1 BBBBBB
> etc. etc...
> I have one interface where the user will select the products that he is
> interestered with. Mean One user may select product ID 1, 3, 5 and other m
ay
> select 2, 7.
> I have to save the product info selected by that user in one Column of the
> user table.
> (I have to save the selected product in one column only)
> So What Type will be for that Column and how do i implement logic to save
> all selected product so that it will be easy to save and extract the value
s
> also.
> I think I can use some binary kind of valu for each product and save the
> selected binary combination or similar logic to store and retrieve.
> Please suggest.
> Thanks
> Prabhat
>
>|||However this is the example of bad DB design where you keep multiple
data in single column, you can make that column of varchar type and
build a string of product ID and use comma (,) separator to saperate
each Product ID.
While fetching you ll only need to split IDs from comma|||What is Bad Design ? Normalization or CSVs for OLTP:
IF you forget about Data intigrity and future intigration problems only
then you can use comma seperated values
this sipmle scenario is not possible with csv.
If you want to write a query to select particular product selected by
multiple users,
is it possible when u use CSV.
Regards
R.D
"Zero.NULL" wrote:

> However this is the example of bad DB design where you keep multiple
> data in single column, you can make that column of varchar type and
> build a string of product ID and use comma (,) separator to saperate
> each Product ID.
> While fetching you ll only need to split IDs from comma
>|||Hi R.D.,
You are right. I know the concept and I know we can use 3 tables for that.
But My question was, Is there a better way of calculation / procedure that
we can store all selected value in one column?
Also the Comma separated method is very critical, as if products will
increase we need to increase the width of the field.
Thanks
Prabhat
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:4A593B0A-B72F-4D7F-9FD4-43E851E2C70B@.microsoft.com...
> Prabhat
> Use RDBMS. Split the User table into produtsSeclected and use usertable
for
> storing base information only. then you will have three tables
> Products where product id is primary
> users where users id is primary
> and Junction Table : Products selected with foreign keys of productid and
> userid. thus participating in many to many relationship.
> Regards
> R.D
>

How to RollBack the data?

Hi,

I am have database with Category_Product table

Category Product

cat1 prod1 cat1 prod2 cat1 prod3 cat1 prod4 cat2 prod1 cat2 prod2 cat2 prod3 cat2 prod4 cat3 prod1 cat3 prod2

i have run the query

UPDATE [sample test].[dbo].[Category_Product ]

SET [Product] = 'prod1'

WHERE [Product]='prod4'

COMMIT

Now the Category_Product is changed. But i need to Rollback the Updated statement execution. So that only changed "prod1" can be replace with orginal value "prod4"

Can any one help me to get back the original data by writing query

Thank you

With the limited information you gave us, I assume that this may help you:

UPDATE SomeTable
SET Product = 'Prod4'
FROM SomeTable S
INNER JOIN
(
SELECT MIN(YourPrimaryKeyWhichYouDidNotmention) PK
FROM SomeTable
Where Producr = 'Prod1'
GROUP BY Category,Product
HAVING COUNT(*) > 2
) SubQUery
ON S.YourPrimaryKeyWhichYouDidNotmention = SubQUery.PK

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi,

Thanks for ur reply.

My table has no primary key.

It has 3 fields Category,Product and Quantity.

The query which u sent me updates 2 records for each category with Prod1 (one is the original with Prod1 and the other which has been changed from prod4 to prod1).But i want to modify only the records which are changed before from prod4 to prod1.

please help me out as soon as possible.

|||

Having the need to identitfy the rows which has to be updated, you should create a PK on the table (even if only temporary using an Identity) and run the query mentioned. It will pick only one row (using the MIN aggregate) and will update this accordingly to your described situation.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to retrieve data of a product which falls under two categories?

I'm using MS SQL 2000. I developing a shopping cart where on the admin side when we are inserting or updating the products, there is a chance that a single product can fall into two categories therefore i'm giving the users a option of CheckBoxList control for the categories. The checkboxlist control is Database Databinded with categories.

When a user selects couple of checkboxes, i'm storing both the categories seperated with a comma in a single field of database but then when i want retrieve the all products of a particular category, this approach wont work. Is their any way around for this??

Even though i use CheckboxList control is their any way to retrieve products of a particular category?? Can i store each checkbox text in a different row with the same product ID's in a different table and all the product info in a different table ?? I hope i made myself clear.

Thanks for your help and time in advance.

Can anyone help me out with the above problem please??

How to retrieve data of a product which falls under two categories?

I'm using MS SQL 2000. I developing a shopping cart where on the admin
side when we are inserting or updating the products, there is a chance
that a single product can fall into two categories therefore i'm giving
the users a option of CheckBoxList control for the categories. The
checkboxlist control is Database Databinded with categories.
When a user selects couple of checkboxes, i'm storing both the
categories seperated with a comma in a single field of database but
then when i want retrieve the all products of a particular category,
this approach wont work. Is their any way around for this'
Even though i use CheckboxList control is their any way to retrieve
products of a particular category' Can i store each checkbox text in a
different row with the same product ID's in a different table and all
the product info in a different table ' I hope i made myself clear.
Thanks for your help and time in advance.it's not particularly clear I'm afraid.
It's a good idea to make sure that all data stored in your database is
atomic (just one value). by storing data with comma separators you are
breaking with this, and it is the start of a very bad road to go down,
as you have just discovered.
One possible answer to your question if you are to insist on keeping
with this data structure is to match on WHERE Category LIKE '%' +
@.Category + '%' . However if you have a category that is a subset of
another (e.g. Name and product name) then you'll have problems with
this.
A much better idea is to store your data in a relational fashion, so
from what I can tell (which is a bit of a guess), you'd want 3 tables
here:
tblCategories (CategoryID int, Category varchar(50))
tblProducts(ProductID int, Product varchar(50))
tblProductCategories(ProductID int, CategoryID int)
the first 2 tables store your information about the 2 entities, the
third stores the relationship between the 2.
You could then in your UI call something to insert rows into
tblProductCategories for each checkbox, then you could more easily
query your data.
Hope this helps, otherwise you might need to post a bit more
information about what you need, I especially don't understand how the
checkboxes fit in to all this.
Cheers
Will|||Thank you very much for your time and help
I think i got the way out with the advice you gave me
thank you very much for that
I have worked out the following way and its working
i know there might be more efficient ways of doin this
As said above i got three tables
tblCategories (CategoryID int, Category varchar(50))
tblProducts(ProductID int, Product varchar(50))
tblProductCategories(ProductID int, CategoryID int)
on the UI side whenever I'm inserting the Product info
i'm grabbin the last inserted Product ID using @.@.IDENTITY
now, i'm loopin the each item in checkboxlist
and if Selected then inserting the CheckboxID (CategoryID)and the
previously grabbed ProductID, therefore the product key will
be same for all the categories.
And now by the joining the Products table with the ProductsCategories
i'm able to retrieve all the necessary info about Products
Thanks mate