SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot miner2049er's Avatar
    Join Date
    Apr 2004
    Location
    Somewhere Out There
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to show different attributes for different categories

    Before I ask my question, let me describe the situation. I'm programming a product directory, where products belong to a certain category (or multiple categories.)

    Currently, all products, no matter what category they are in, have the same type and amount of attributes - for example name, description and price. I wish to make it so that products contain attributes depending on what category they are in - many products will have attributes in common - such as a name and description - then there will be some attributes not in common, such as books having a "hardback or paperback" attribute which would not be applicable to the DVDs category.

    Attributes can be any value - boolean, numeric, text etc.

    Also, a product can belong to multiple categories, and it would have attributes from all of those categories it belongs to. But it would only have attributes of the categories it is in - not inherit any from grandparents i.e. the parent category of its category(s).

    As an example, for a "mobile devices" catalog:

    available attributes:

    1 name
    2 description
    3 GSM frequencies 800, 1800, 1900 (a set)
    4 audio formats MP3, WMA (a set)

    categories:

    1 phones
    2 audio players


    Products in category 1 have attributes 1, 2 and 3.

    Products in category 2 have attributes 1, 2 and 4.

    Products belonging to both categories 1 and 2 (say a phone that plays audio files) would have all attributes 1-4.

    In the following paragraphs, when I say add "edit" something - I mean either adding that item to the db, or modifying or deleting it.

    I want to be able to edit products as expediently as possible, and I don't want to have to choose what attributes to select for a product every time I edit a product. In other words, whenever I edit a product, after choosing what category(s) it belongs to, I am immediately presented with all the attributes I need to fill in for that product - attributes are attached to a category rather than a product.

    I envisage a number of editor screens:

    The "attribute editor" screen is where I edit the attributes that are potentially available to all categories.

    The "category editor" screen is where I would edit category attributes - it would display something like checkboxes allowing me to choose what attributes go into this category. On this screen I would also choose the parent category of the category.

    The "products editor" screen is where I edit products - where I choose a category(s) for a product, and fill in the values of attributes for that product.

    Currently attributes (such as name & description) are embedded in the product detail rows, but I want to break the attributes out of there.

    My question is what would be my data schema for attaching attributes to categories (and thus their products) in an efficient, normalized way?

    Should I attach attributes to products with a products_attributes table containing rows such as:

    id_product / id_attr / id_value

    or should I attach attributes to categories - with an attributes table along with a category_attributes table, such as:

    table_categories: id / id_parent / name

    table_attributes: id / name

    table_category_attributes: id / id_category / id_attribute

    But what about those attributes common to all categories such as name and description?

    This is where I start to get lost when wondering how to store the actual data for each product in the db, and how to present it for editing to the user when they add/edit a product (i.e. to know what attributes available.)

    I want no repeated data, so even if a product appears in two categories, its name etc. is only stored once in the db.

    Any advice would be appreciated!

    Miner2049er

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I envisage a number of editor screens:
    Hi, it's more convenient to attach/post links on your UI prototype screens

    Attributes can be any value - boolean, numeric, text etc.
    The information about attribute' datatype (boolean, numeric, text etc.) should be stored anyway, but to simplify your data model you may want to store all attribute values as string (VARCHAR datatype) like 'product_attribute' table which is shown below

    Also, a product can belong to multiple categories, and it would have attributes from all of those categories it belongs to. But it would only have attributes of the categories it is in - not inherit any from grandparents i.e. the parent category of its category(s).
    This simplifies the task

    Should I attach attributes to products with a products_attributes table containing rows such as:

    id_product / id_attr / id_value
    You need to "attach" a product to categories first:

    product
    --
    id
    name
    descr
    price

    If your model allows more than one price (discounts, etc.) for a product, product prices should be in a separate table or treated as another product attribute

    category
    --
    id
    id_parent
    name
    descr

    category_product
    --
    id_product
    id_category

    'product_attribute' table stores attributes and their values for every product
    product_attribute
    --
    id_product
    id_attribute
    value

    If you want to store attributes using their datatypes boolean, numeric, text etc., you may implement it either using one table for every datatype or using one table with one column for every datatype

    or should I attach attributes to categories - with an attributes table along with a category_attributes table, such as:

    table_categories: id / id_parent / name

    table_attributes: id / name

    table_category_attributes: id / id_category / id_attribute

    But what about those attributes common to all categories such as name and description?
    What's wrong with common attributes (name, description, etc.) except they may be stored in the 'product' table? BTW you don't need 'table_category_attributes.id' column if 'table_category_attributes.id_category' and 'table_category_attributes.id_attribute' columns will comprise PK. This model is all you need to allow many-to-many relationships between 'table_categories' and 'table_attributes' tables, i.e. "a Category may have zero or more Attributes" and vise versa

    I want no repeated data, so even if a product appears in two categories, its name etc. is only stored once in the db.
    If the product belongs to Category A and Category B, appropriate data about that fact should be stored in the database somehow, i.e. no name, description, etc., but its identifier (PK ID) should be stored/linked to both categories. Another thing is when Category B belongs to Category A, application logic, for instance, should store ID of the lowest subcategory only the product belongs to

  3. #3
    SitePoint Zealot miner2049er's Avatar
    Join Date
    Apr 2004
    Location
    Somewhere Out There
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Thanks for the reply. Yes, having data stored as a string allows me to transform it into many other types, including arrays through serialization.

    Does anybody have any comments on the following schema? (with demo values.) At first glance it seems to be OK, but I'm not sure if there are any hidden gotchas...


    table_category_products

    id_category - id_product
    ----------- - ----------
    1 - 1
    2 - 2
    1 - 3


    table_attributes

    id - name
    -- - ----
    1 - title
    2 - condition
    3 - mileage
    4 - covertype


    table_categories

    id - name (parent category info also in this table)
    -- - ----
    1 - books
    2 - cars


    table_category_attributes

    id_category - id_attribute
    ------ - -------
    1 - 1
    1 - 2
    1 - 4
    2 - 1
    2 - 2
    2 - 3


    table_product_attributes

    id_product - id_attribute - value
    ---------- - ------------ - -----

    1 - 1 - honda civic
    1 - 2 - falling apart
    1 - 3 - 500,000
    2 - 1 - lord of the rings
    2 - 2 - almost new
    2 - 4 - hardback
    3 - 1 - fire in the valley
    3 - 2 - slightly used
    3 - 4 - paperback


    Thanks in advance for any replies.

    Miner2049er.

  4. #4
    SitePoint Zealot miner2049er's Avatar
    Join Date
    Apr 2004
    Location
    Somewhere Out There
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I also talk more about this topic in a new thread "Craigslist data schema" at:

    http://www.sitepoint.com/forums/show....php?p=2098178

    Miner2049er.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •