Shoot me now, I need JOIN help

[eluser]slowgary[/eluser]
That would work too. Although that method allows the database table to be 'broken', where more than 1 image could be checked as primary. Is one way better than the other?

[eluser]slowgary[/eluser]
BTW Jedd I would LOVE any scrutinizing of my schema. I really want this to be made well, which is why I probably should've had someone ELSE do it. You know what they say, "If you want it done right, get someone else to do it".

[eluser]Evil Wizard[/eluser]
what I would do is have a table with products in, a table with image/media data in then a third table holding the relationships between the product and image. That way products can have more than one image, images can be used for more than 1 product and it's easier to use a join to get the data you want.

[eluser]jedd[/eluser]
I don't like the idea of a link from products to a specific product_image to denote which one is primary, though that is probably the neatest way .. just doesn't sit well.

You are right - your integrity could break if you have a flag in the product_image table to denote primary, but if you LIMIT 1 on selects this won't matter hugely. Alternatively just stick with the LIMIT 1 thing, and I think there's a random option for your selects - which might be an interesting feature if you want to cycle through primary product images.

A quick browse at your column names .. I'd really avoid the confusion of having 'cat_id' as the primary key for two different tables. I'm a big fan of just 'id' as the PK for every table, though a recent discussion in another thread suggests an alternative standard of tablename_ as the prefix for every column. I think having cat_ as the prefix for every column in a table called categories is an example of something else that'll confuse you in the months to come.

What does the quantity refer to in the product table?

What's cat.cat_image point to? (It sounds like the product primary image idea above.)

[eluser]slowgary[/eluser]
The quantity is the actual quantity on hand. Bad name for it? Or should there be another table for instances?

I'm not using category images right now, but I made cat_image for that purpose.

[eluser]kgill[/eluser]
[quote author="slowgary" date="1243456101"]That would work too. Although that method allows the database table to be 'broken', where more than 1 image could be checked as primary. Is one way better than the other?[/quote]

While it means it can be broken, that's only going to happen if you're careless and let it be, this is why you've got a model, in addition to pulling data out of the DB it should be ensuring what's going in isn't going to screw up things. Somewhere in whatever you've called the function set_image_as_primary($product_id, $image_id) you check does this product already have an image set as primary, yes? update that row to remove the flag and then set the new image as primary or alternatively throw an error saying you can't do that, whatever you think is the appropriate action to take.

[eluser]slowgary[/eluser]
I understand and agree. It still means it's possible to have it broken, even if it requires someone going in and manually breaking it. It also requires more code.

I've opted for having a new field in the products table which will point to the primary image. This way won't allow the possibility of multiple primary images and requires less code.

Something still doesn't sit right with having two tables, each with a reference to the other. Not that it won't work or that there will be any detriment to performance, it just seems like not the best thing to do. I'm okay with it though.

[eluser]Evil Wizard[/eluser]
[quote author="slowgary" date="1243458532"]The quantity is the actual quantity on hand. Bad name for it? Or should there be another table for instances?

I'm not using category images right now, but I made cat_image for that purpose.[/quote]
By "in hand" do you mean "in stock"? if so then wouldn't stock be a more descriptive fieldname. I leave a qty field for ordered items

[eluser]slowgary[/eluser]
[quote author="Evil Wizard" date="1243516127"]wouldn't stock be a more descriptive fieldname. [/quote]

Yes it would be. Changing it RIGHT now.

[eluser]Evil Wizard[/eluser]
[quote author="slowgary" date="1243535637"][quote author="Evil Wizard" date="1243516127"]wouldn't stock be a more descriptive fieldname. [/quote]

Yes it would be. Changing it RIGHT now.[/quote]
Happy to be of assistance