I'm using a MySQL backend on a PHP project - this question is more SQL design, but I like and trust this group much more than the other resources I have (even though they are few) so I'm posting it here. I'd be more than willing to chat about this after-hours at the Aug. meeting if that's more appropriate than this mailing list.
I've got a scenario similar to a retailer: I want someone to be able to see all the items they have purchased. Overly-simple illustration:
Table 1: ItemID, ItemDescription
Table 2: UserID, UserName
What's the most appropriate way to connect the User with ItemsPurchased?
For example, my thinking so-far is to have add a field in Table 2 called "ItemsPurchased" which would be a comma-separated list of ItemIDs (Table 1) the User has purchased. If I want to show a list of what they've purchased, I can .explode. "ItemsPurchased" and .foreach. my way through the returned array.
Is this bad design?
For the record, I am in NO hurry on this and it is NOT critical!