Table problem

[jeremywatco]

Here is what I am trying to accomplish. Its a little more challenging then I thought. Maybe you guys can point me in the right direction. I have two tables. One Table contains users and the other table contains products. Each record in both tables have a field named group_id . This field contains a series of numbers to distinguish access to resources. I need something that will look at all the numbers a specific user's group_id field and see if any of the numbers match any of the numbers in the product tables group_id field. When it finds a match it needs to display the product name.

Any ideas would be great! :) The numbers are comma delimited. IE. 1,2,10,21,23

[Spudhead]

I might be wrong here, but I think good database design stipulates that this sort of relationship (there's a name for it, but I've forgotten) is managed by three tables.

You'd have users, products, and user_products.

User_products would simply contain a user_id and a product_id - and thus you can run simple SELECT statements to find matching user/product combinations without having to search through strings etc.

Any use?

[whammy]

Actually spudhead is right, I think - by comma delimiting the field you are making things way too complicated, and I don't know any way besides using the IN() command in SQL in which you can use a comma-delimited value (although you may be able to shorten the example idea below with this IN() somewhere).

I would try three tables:

1. users
2. products
3. userproducts

That way, you could have as many records in the third table as you want, that are related to the users and products table. Instead of comma delimiting them (i.e. 1,2,3,4), you would have a separate entry for each formerly comma delimited value in the third table - each record in the third table would contain a "usersid" and a "productsid", which would be the secondary key in those tables - and you could have as many of those as you want; and you could join on the other two tables to get the exact results you want.

Someone correct me if I'm wrong, but this seems a bit more sane to me...

And I hope that makes sense. Its kind of hard for me to explain concepts in writing that I understand visually. :)

[jeremywatco]

Thanks. I will give it a shot.

[Roy Sinclair]

When you do that it's called "Normalizing" the database. Here's a link that'll give some good background on the process and why you would want to do it.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887&pg=1