I've been working on a site on and off as time has allowed me ( drinksforall .com [w/o spaces]) and what I was trying to do was to create a members section where members can bookmark certain drinks that they like, this way, they can have their own list of drinks that they find useful without having to search for it every single time. I can't seem to wrap my head around the logistics of how I would go about storing this information in the mysql database, what kinda of structuring would allow me to do this? I want each user to be able to have a referered of however many drinks they'd like, but How can I store all the drink references in one field in such a way that i can still differenciate between the references (specifically the drinkids that are contained in the drinks table)? Any ideas would be appreciated?
I'm not sure how complicated your data model is...I may be oversimplifying this but I envision you having 3 basic tables (maybe much more for sub attributes, etc). A table for the customers and their profiles, a table for the drinks, and then an associative entity table that cross references drinks to customers. With the associative entity, a customer can reference many drinks, and a drink can be referenced by many customers. Tim
right, and that's how I was thinking, but that cross-referencing seems to be the problem. Lets say Member 'Alex' decides to add drink 'Jack blank' to his favorites. Now, PHP goes to mysql and tells it to add a drink id, the member id, to two fields in a single row in the cross-referencing table. Now what if this same member wanted another drink, another entry is made into this table with member id and drink id. But what happens when another member wants the same drink? do we make a whole new entry? Even though the drink id might be the same as another member? Wouldn't this get redundant? Is this really the only way? sub-attributes wouldn't be a problem since those can just be pulled from the drinks table via drinkids and members table by member id accordingly. I guess I am not understanding the associative entity part too well, maybe if you could expand a little more on that? btw, thanks for taking the time to reply, I wasn't sure if anyone actually would.
I agree with the 3 table suggestion. Profile table with personid, drinks table with drink id, and the person_drink table with personid and drinkid and maybe other things like date and so on. When josh adds "jack blank" to his favorites, it would add the person id and drink id to the person_drink table and keep on doing this for every person wanting to add to their favorites. And to pull a list of josh's fav's just do an inner join (or whatever you need) on the person_drink table on either the person id or the drink id.
I could do three different queries? One followed by the next? IE: If member is logged in: Fetch Member info from members table Check and fetch all references from bookmarks table where member id=member id from member's table create drink links using the drinkid references (to fetch drink information as the link for the bookmark is clicked, or perhaps provide the option to view all drink information in one page, but i foresee too many problems associated with this, so i'll probably choose just the creating link to actual drink pages instead) I think i see what I need to do to make this work now! Thanks alot guys, been VERY HELPFUL! I'll be sure to share the finished product!
Yes, when a second member adds the same drink as member A, a new record goes into the associative entity table. This table should be pretty lightweight, storing only it's own UID, the UID of the person and the UID of the drink, and maybe if the user can rank their own drinks then the ranking data would go here to. A user faving a drink is a unique piece of data which must be stored, I dont see any other way than using the associative entity table. Good luck! Tim
I think I have a system in place for the above. Although the site is not done yet, if anyone wishes to play around and test the functionality of the member's area for me, it would be awesome. http://www.drinksforall.com is the link to the site, you will have to add yourself as a member (don't worry, very un-intrusive information). for those who would rather not take the time to try the signup part, heres the demo user info user: demo pass: demo