Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006

    Best way to store multiple attributes?


    what would be the best way to store attributes for a certain table, when one doesn't know the upper limit of the attributes.

    Consider the following two tables

    Table: Attribute
    Fields: AttributeID

    When a product is created the user can select which attributes are present for that product.

    For now I know there are 5 attributes in the scenario I'm working with, so I've created the product table as follows
    Table: Product
    Fields: ProductId

    Now I'm sure this is very clunky design. Especially if the user gets back to me and says he wants to add more attributes.

    Is there a better way to store this sort of thing?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    there sure is -- store one row per product attribute

    Table: Product
    Fields: ProductId

    Table: ProductAttributes
    Fields: ProductId
    AttributeId | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    ARGH! Why didn't I think of that!

    Thanks a bunch!

  4. #4
    Join Date
    Jul 2006
    Ok, so I implemented the attributes this way, so a user can select any number of attributes. YAY!

    Now another query to check if I'm doing things the best possible way.

    Now it's possible for a user when they're creating a new product type to specify the kinds of attributes they want for that product.

    I've implemented this by providing checkboxes to the user, so they can select which attributes they want a particular product to have.

    Now, they can, of course, edit this later and uncheck certain attributes and/or select new ones.

    Here's an algorithm of what I'm doing now:

    Loop through attributes in form
         If attribute checked
              select * from ProductAttributes where attributeId = attributeselected
              If EOF
                 insert into ProductAttributes...
              End If
             select * from ProductAttributes
             Loop until EOF
                 If checked in ProductAttributes table
                      Delete row
                 End If
             End Loop
         End If
    End Loop

  5. #5
    Join Date
    Sep 2002
    Sydney, Australia

    Multiple Attributes Normalised ...

    1 I think it may help to understand that the idea of relational dbs and SQL, is that it is a set-processing paradigm, which is also called client-server: the client sends a batch of SQL (request) to the server; which processes the SQL; and returns a result set. What you are doing in post #4 is a record-processing paradigm, for which SQL was not designed, for which you have to turn the set-processing engine upside-down (different suppliers provide different degrees of capability, but it is "not SQL"). Therefore you may want to look at a application coding language (or objects/classes) that have result set-processing capability, that is they have objects/windows that handle a whole result set which is received from the server.

    2 In the set-processing world:
    select AttributeId, Checked="Y" from ProductAttribute where ProductId = @ProductID
    union all    -- product specific
    select AttributeId, Checked="N" from Attribute where AttributeId not in (select AttributeId, Checked="Y" from ProductAttribute where ProductId = @ProductID)
    -- this will give you a result set, of a full set of possible Attributes for the given Product, with Checked = {Y|N}, which you will load into memory (object, DataWindow, whatever)
    -- display the set (“form” that allows changes to Attribute for a Product)
    -- allow the user Un/Check the box/column of the set/form in memory
    -- track the changes to columns in memory (if FieldChanged ...)
    -- upon SaveClicked, for each changed row in the set/form in memory:
    --     if Checked then insert ProductAttribute ... 
    --     if Unchecked then delete ProductAttribute ...
    If you do not have FieldChanged capability in your form/set object, simply save a copy “Original” of the object before you display it, and compare the changed set against the original, to determine which rows/fields have changed.

    For the last two lines in the code above, I have used simple SQL pseudo-code for illustration. You should expand the two lines into a proper transaction with error checking.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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