Attribute system

[Carl Smith]

I wish to move from a hierarchical category structure to an attributes system whereby I can look at/create a product and choose any attribute that I think it matches.

The attributes are not part of the Product table, but they need to be presented to the user as being available for selection and I need to compile records into a Product Attribute table.

My initial thoughts are that I will require x4 tables,

Products

(to include Parent Key = Product ID as well as a Global field to store the Attribute Group ID so that I can filter the Attributes)

Product Attributes

(to include Child Keys = Product ID, Attribute ID)

Attributes

(to include Parent Key = Attribute ID, Attribute Name - text value, Child Key = Attribute Group ID)

Attribute Groups*

(to include Parent Key = Attribute Group ID, Attribute Group Name - text value)

(*-primarily used in order to filter the Attributes)

My ideal solution would be for the user to be on the Product Table, create and fill in the Product fields, then - say via a Tab Panel - select a group of Attributes and then check against each one that is relevant, using a "check box" and as this is happening a record is being created in the Product Attributes Table (conversely if the Attribute is de-selected then the Product Attribute record would be marked to be deleted).

I cannot get my head around,

a) how you can display the Attributes to get the text value (e.g. Attribute Name) together with a "check box"

how to get the Attribute ID into the Product Attributes table when selecting the named Attribute

c) how, when viewing the record at any time, to show which attributes have been selected already

An example might be that I have a music book of Disney songs for Flute. The fields on the Product Table will provide the book information (e.g. Publisher, etc.) but could be have the following "categorization",

Wind Instruments

Flute

TV & Film music

Disney music

Easy music

In the hierarchical structure I end up down at level 5 (Easy music) if I go the above route (with no other options), but I want to view Easy music that just happens to be for Flute!

A little long-winded, I know, but I would really appreciate some help on this.

Thanks

[comment]

a) how you can display the Attributes to get the text value (e.g. Attribute Name) together with a "check box"

how to get the Attribute ID into the Product Attributes table when selecting the named Attribute

c) how, when viewing the record at any time, to show which attributes have been selected already

a) In a portal to Attributes, based on matching the selected (i.e.global) gAttributeGroupID in Products to the AttributeGroupID in Attributes.

By loading it into a variable (or a global field in version 5*) before going to the ProductAttributes table to create a new record.

Note that some scripting is definitely required here. Although you COULD create new records in ProductAttributes by checking a box, there's no way you can DELETE an existing record by unchecking it. So it's best to make the checkbox a button that creates/deletes a related record, while the checkbox field itself would be merely an indicator.

c) By showing them in a portal to ProductAttributes (or to Attributes directly*).

---

(*) Your profile shows version 5, yet you speak of a tab panel. There are vast differences between versions, and no specific advice is possible when we don't know your version capabilities.

[Carl Smith]

Hi comment

Thanks for your swift response. I'll try and set up a db using your ideas (and I've updated my profile!).

Best Wishes