Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am constructing a pivot table with many columns (300) and I wonder if I this is a bad practice.

Here are some details why I need to contruct this table.

A (very) simplified presentation of my database :

I have a company with diverse glass products, windows for example. Each product is referenced by and ID and has (many) attributes.

Products

ID | Attributes | Value
1  | width      | 10
1  | height     | 15
1  | refraction | 1
1  | thickness  | 0.5
1  | rating     | 10
...

Now, I would like to add some (computed) attibutes

For example a new attribute surface should be defined by surface=width*height. To do this, I create a pivot table from the Products, from which I create the computed attributes such as width*height AS surface.

With this method, I can make a query depending on the surface.

Is this a bad practice to do so, since the number of columns can be very high (300) in my case ? If there is another way to do this kind of operation, you can let me know.

By the way, this exceeds the maximal number of columns of Access...

share|improve this question
4  
In my experience MS Access can have performance issues and if you are trying to pivot 300 columns you might run into issues. You might want to look at doing this type of conversion in the presentation layer instead of in MS Access. –  bluefeet Dec 17 '13 at 17:12
    
The computed attibutes, such as the surface will be used in some SQL Query, for example, by selecting all products with a given surface. This is my limitation in the present case. –  motsdujour Dec 17 '13 at 17:17
2  
I would break your query into multiple queries so that you have far less than 300 columns in the output of any given query... as @bluefeet said, Access will have performance issues with a query this complex. –  Max Vernon Dec 18 '13 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.