Best way to organize data?

Wired1

I'm working on a project that involves many, MANY items. Organizing them and categorizing them is the easy part.

Let's say there are 50 end categories with x amount of items in each one (yes, x will vary between categories). Each item has somewhere between 5 and 20 specs that define it. The items within an end category share the same descriptors. Certain descriptors may be used in more than one category, and because all the items are related in one way or another, some values may be in more than one descriptor.

For example:
the circle category items have the descriptors radius and color
the rectangle category items have the descriptors length, width, and color

So anyway, here's where it gets messy. each descriptor can be one of y possibilities. Of course, x and y and the specs are finite, but there's something like.... 100,000 combinations, at least! Trying to figure out a clean way to design it all.

One way:

| unique_id | item_id | descriptor | value |
--------------------------------------------
|     1     |    1    |   length   |   5   |
|     2     |    2    |   radius   |   20  |
|     3     |    2    |   color    |  blue |
|     4     |    3    |   side     |   30  |
|     5     |    3    |   color    |  red  |
|     6     |    3    | opaqueness |  .05  |
--------------------------------------------

another way:

| unique_id | item_id | spec_id |
---------------------------------
|     1     |   525   |   335   |
|     2     |   525   |   237   |
|     3     |   173   |   832   |
---------------------------------

spec_id table:

335 | color red
237 | radius 25
832 | color blue

I can see pros and cons to both ways. Does anyone have a better way to do this? Comments / suggestions are appreciated! Yes, I could make like 100 table and end it there, but I think I'd go crazy writing that many create / edit / delete forms lol!

And in case my description wasn't clear, think of the forms behind updating items for Amazon. Nope, site's not gonna be that big, nor is it a store, but it could get that complicated unfortunately.

leatherback

Hi,

Personally, I went with the second option (assuming you will add a value colum to the first table), in a project where I wanted to store metadata for scientific data of our department. Since I did not know what data would be stored, I basically created a system like the second system you describe.

This prooved most flexiable with regards to updating labels, and expanding label-items to your descriptions ( e.g. you want to add units to the specs: All you'd have to do is add this to the descripption table, and not top the actual record for each item, and updating a label is must easier).
An other advantage may be that you can use the descriptio table for whatever may come up.

Don't worry about size. Queries on these ID-type colums are very fast.

Wired1

| unique_id | item_id | desc_id |
---------------------------------
|     1     |   525   |   335   |
|     2     |   525   |   237   |
|     3     |   173   |   832   |
---------------------------------

spec_id table:

| desc_id |  spec  | value |
----------------------------
|   335   | color  |  red  |
|   237   | radius |   25  |
|   832   | color  | blue  |

Like that you mean?

I may change spec to spec_id and have them in a different table.