On Jul 26, 2006, at 1:52 PM, Jeff B wrote:
> > Yeah it's a bit hard to explain. I hope to do better this time. > > To preface, the reason for this schema I'm about to explain is because > I have many different item types and each one has a different set of > attributes that I want to store. The desired set of attributes that I > want to collect can also change, sometimes frequently. > > When trying to layout the schema with tables for each item type, we > started looking at a lot of tables, with a lot of complex rules and > relations. The kind of schema that makes you run away screaming. > > We came up with this alternate schema which uses a 'virtual schema' or > definition for each item type is stored in a table. So instead of the > traditional table like this: > > Table: cogs > Fields: name, model, size, color > > We have something like this that replicates it: > > Table: item_types > Fields: id, name > Insert Into: 1, cogs > > Table: item_types_attributes > Fields: item_type_id, attribute_name, field_type > Insert Into 1, model, text > Insert Into 1, size, text > Insert Into 1, color, text > > The data in those two tables describe to the application the kinds of > data to collect for a cog. > > Then when storing the cog data it is handled much the same way. The > table structure is like: > > Table: items > Fields: item_type,name > Insert Into: 1, cog1 > > Table: item_attributes > Fields: item, attribute, value > Insert Into: cog1, model, 7500 series > Insert Into: cog1, size, large > Insert Into: cog1, color, blue Okay so to preface my answer, this is really a crazy setup, and I hope you've exhausted quite a few other options before trying this. :) Cake should be able to handle something like this, though it might take some massaging because some of your foreign keys are compound and text based (because when you fetch and Item, you're going to want its ItemAttributes and the ItemAttribute's associated ItemTypeAttributes). The naming you've provided above uses conventions that aren't cake friendly, so you'll also need to tell your models where things are. If you run into too many problems, you might try to write methods in your models that fetch the data you need. For example, Item::getAttributes(). Basically, there isn't going to be an easy way to do this no matter what software you use, though Cake could probably get you quite far. -- John --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php -~----------~----~----~----~------~----~------~--~---
