I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.
Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column.
Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:
_Products.DesignId = _SubCategories.SubCategoryId
DesignId = COALESCE (@DesignId, DesignId)
But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table.
In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?
Thank you guys for the help. It is much appreciated.