If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Best way to store multiple attributes?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-06-06, 07:55
Jugular Bean Jugular Bean is offline
Registered User
 
Join Date: Jul 2006
Posts: 11
Best way to store multiple attributes?

Hey,

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

Eg:
Consider the following two tables

Table: Attribute
Fields: AttributeID
AttributeName

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
ProductName
attribute1
attribute2
attribute3
attribute4
attribute5

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?
Reply With Quote
  #2 (permalink)  
Old 08-06-06, 08:15
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
there sure is -- store one row per product attribute

Table: Product
Fields: ProductId
ProductName

Table: ProductAttributes
Fields: ProductId
AttributeId
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 08-06-06, 08:26
Jugular Bean Jugular Bean is offline
Registered User
 
Join Date: Jul 2006
Posts: 11
ARGH! Why didn't I think of that!

Thanks a bunch!
Reply With Quote
  #4 (permalink)  
Old 08-07-06, 02:29
Jugular Bean Jugular Bean is offline
Registered User
 
Join Date: Jul 2006
Posts: 11
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:

Code:
Loop through attributes in form
     If attribute checked
          select * from ProductAttributes where attributeId = attributeselected
          If EOF
             insert into ProductAttributes...
          End If
     Else
         select * from ProductAttributes
         Loop until EOF
             If checked in ProductAttributes table
                  Delete row
             End If
         End Loop
     End If
End Loop
Reply With Quote
  #5 (permalink)  
Old 08-08-06, 00:07
DerekA DerekA is offline
Registered User
 
Join Date: Sep 2002
Location: Sydney, Australia
Posts: 255
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:
Code:
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On