Let's say I have a Product and a ProductType table. I want a table where I can store special columns for each product type. The special columns (attributes) can be of any type.
Right now we have a table per ProductType called Product_%ProductTypeId% which is a solution I really don't like - any suggestions ?
My idea was to have a table ProductTypeColumns with cols:
Id | ProductTypeId | ColumnName | Value | ColumnType
what I don't like about this is that I'm losing type safety, column Value would be a string type which would mean I have to always convert to and from.
Plus this table will be used in generating reports.. having "dynamic" columns may be a problem.
This is called EAV.
You can find some questions about EAV and SQL Server here on SO:
It's a subject where lots of different people will have different opinions.
Some will say that you should stick with Product_%ProductTypeId% tables, and some will say that your ProductTypeColumns table is the better way.
Basically, I'm in the ProductTypeColumns camp.
We are using something similar at work as well:
Our table contains attributes for order items (over a million order items and over 250 million attributes) and we are using it for nearly ten years now.
Disadvantages: no type safety (as you already mentioned) and querying can be a bit complicated.
But for us, it's the only possible way because we have over 1000 attributes, over 100 products and each product can have 100 to 300 attributes. With dimensions like these, an attribute table is the only possible way.
But it's probably not the best solution for everyone and I don't know if it's the best solution for you.
If your number of products and special attributes is not that large, maybe you can get away with your first suggestion (Product_%ProductTypeId% tables).
That would definitely be easier to query, but maybe it's not an option if there are too many possible combinations of products and attributes.
As often, it depends.