Hi Garbee long time no see =P

I'm actually working on something similar.

Your tables make sense, but your products table has columns that you probably wont be needing, since you already have them on the variations table.

What i'm probably considering on doing is, having a "decoupled" table or tables to store the options and the values (ex: Color - red, blue, yellow | Size: s, m, l etc..)

Then i can create option sets where i can have both Colors and Sizes and when i go to my product i just choose the option set and i can choose the values (red, yellow, etc..) for each option

It's a bit hard to explain, but i'm thinking this way because, not all, T-Shirts for example, will have the same colors or sizes and this will allow me to create the variations accordingly automatically or manually.

If you want, you can ping me on IRC tomorrow as we can probably help each other since we're working on something similar.

Cheers.

I'm doing a similar thing with my products but am using a single table inheritance structure (https://github.com/uberous/shop/blob/master/app/Uberous/Support/Model.php)

My Property model extends above model and when I use the Stock model only the rows that match Uberous\Support\Stock are returned.

In this way I can store multiple product properties in a single table so that if I need to add one I don't need to alter the db structure.

Yea, I'm avoiding storing variations in the same table. I'd much rather have variations external since they only can have a targeted subset of attributes and the rest are simply inherited from the parent. So I'm reducing duplication all around.

@brunogaspar, on the note of the products table having uneeded things, you are assuming everything would have variations. Base products will set the products attributes. Then a variation based on options may override those default values, however it is not required. Then the disabled boolean to each variation is to actually decide if that variation exists/should be shown individually compared to deleting the record outright (while also inheriting the parent products value if it is set.)

My structure is setup to make searching as quickly as possible and keeping all structured data to the products for different types out in their own tables.

When I get the chance (working at my dads shop today, so not sure if I'll have any IRC time to really talk) I'll hop into the chatroom and we can go over things. I know Cartalyst has a package that is part of what I am thinking (just in a bit more complicated way then I was planning by json_encoding option sets.)