mysql layout

NickPR

Hi there 🙂

Would anybody here please be able to advise me what would be the best way to deal with my following dilema? :

I have 2 tables: products and users

products has the following columns: productid, title
users has the following columns: id name produce

What i'm trying to achieve is a way to associate any of the products from the products table to to each user in the users table. The way i see it is that i could have some form of int like a bitfield that would link to the products table. The problem with bitwise is i can't have more than 32 items? There surely must be a simpler way of doing this, but i'm clueless i'm afraid.

Here's an example of how i would like the data to end up:

table data first:

products

productid title
1 Tomatos
2 Pears
3 Carrots
4 Potatoes
5 Strawberries
6 Cherries

users
id name produce
1 Fred 345
2 John 26
3 Mark 1234

The above joined

id name produce
1 Fred Carrots, Potatoes, Strawberries
2 John Pears, Cherries
3 Mark Tomatoes, Pears, Carrots, Potatoes

Many thanks,

Nick

NogDog

Probably the most typical way would be to create a third, "relation" table. It would just need two columns: user_id and product_id. So for your above example it would have the following rows:

TABLE: user_to_product

userid   productid
------   ----------
1        3
1        4
1        5
2        2
2        6
3        1
3        2
3        3
3        4

Then if, for example, you wanted to know what products were associated with John, you could query:

SELECT p.productid, p.title FROM products p 
INNER JOIN user_to_product utp ON p.productid = utp.productid WHERE utp.userid = 2;

This allows for essentially unrestrained scalability as far as numbers of products and users which may be associated to each other.

NickPR

that worked great thanks NoDog. Is utp the creation of a vertual table in this usage?

NogDog

NickPR wrote:
that worked great thanks NoDog. Is utp the creation of a vertual table in this usage?

The "p" and "utp" are table name aliases, simply used to reduce the amount of typing, for example allowing me to specify a field as "utp.productid" instead of "user_to_product.productid". It's totally optional if you prefer the clarity of using the full table name.