Question about the foreign key relationship between tables.

[Murphdog]

This is my first attempt at creating a database from scratch so please forgive me.

Lets say i have 40,000 items, sepearted by 8 categories (that can be seperated by additional sub-categories like male/female and armored/shielded/enhanced/none). Is it better to create a table for each of the 8 categories, or Create 2 tables, 1 for the 8 categories and 1 for the 40,000 items? More tables for the sub-categories?
(on a side note, each item has almost 50 different attributes so eventually, hopefully, they will be able to be searchable/queried)

I'm guessing the 2 table way would be more streamlined.

Maybe I'm a little confused as to which way to do this, but I have read that there really isn't a wrong way.

To help you understand, here is what I want to have...
8 categories separating types of clothing, seperated by sex, sepearated by Buff Types, separated by attribute (gamers know what this is and there are close to 50)

If I have confused anyone I'm sorry. I am too.

Now if I do use the 2 table way I described above, is it a requirement that the foreign key be INT and aut-incremented? This is the main reason why I'm posting. For some reason i thought ENUM would be better but I'm not so sure.

Thanks for any help
Murphdog

[artacus]

You could use ENUM for simple things like Male/Female. But it sounds like you have a lot of branching (if its male you have one type, female another.)

I wouldn't do a two table approach. The best way is to figure out how to best organize your main table (each individual piece of clothing) and work backwards from there.

I'd make that table look something like this:
-id (autoinc INT) <- not required btw but makes life much easier
-description
-garment_id
-armor_id
-buff_id
-color_id

Make a garments table:
-id (autoinc INT)
-description ('polo shirt')
-gender (ENUM 'male','female','unisex')
-type_id (use a garment_type table with entries like "pants","shirts","hats",etc)

For attributes, it sounds like there is a 1 to many relationship (the same shirt could be 'warm', 'fuzzy','plaid','buttoned', who knows)
So you would all of those attributes in a table then add another table to link those attributes with a specific garment_id.

[Murphdog]

I thought I would post a little more.
Here is a rundown of the categories and how the items could all differ. Clothing names and attribute lists have been shortened for sake of the discussion.

category            clothing name        sex            buff type        attribute
------------|    -----------------|-----------|-------------|----------------
pants        |    abt 40,000 of    |  male      | None          | Ballistic Resistance
shirts          |    them. Many with | Female    | Armored      | Confuse Duration Bonus
shoes          |    the Same name |                | Shielded      | Melee Resistance Bonus
leggings      |                            |                | Enhanced    | etc about 45 more of these
hats            |                          |                |                  |    attributes
outerwear    |
sunglasses    |
gloves        |

[artacus]

Yeah so? Did you get anything from my post?
I would put category in its own table and use category_id, if there are only 4 buff types you can use enum and attribute is a 1 to many so it would go in a separate table.

[Murphdog]

Yeah, got it thanks for help artacus. I'm going with your suggestions. Thanks again
Murphdog