Just looking for some advice on database design. I'm relatively competent in MySQL (competent at a push, haha) and I'm in a bit of a pickle right now when I'm trying to generate products for a Magento store. Well, Magento is where it ends up, I currently use a mass importer called MAGMI which is awesome.
My client wishes to convert his stock levels feed (Sage for anyone who's interested) into information on his website without having to do as less data input as possible.
He sells electronics and white goods and I've been hooked up with a repository of information which basically lists all the information of each product based on model number (found in his feed).
This all works very well, the products get imported and get matched up fine. However, when it comes to attributes I have an issue. There is a huge amount of attribute types in this repository and each product takes up roughly 6 rows in this table - 1 per attribute (so row 1 = Product ID | Attribute Name | Attribute Value etc)
The import tool (magmi) limits me to selecting the products and information in one go, so this is where my problem lies. I need to somehow upload all my generated products and take the attributes along with them. What I want to avoid is having to create 200+ columns in a table and then specifying them all in one query. Actually I'm lying, I have tried this already and it works fine. It's not sustainable though when new attributes come along.
From an experts point of view, what should I be doing here? If Magento's database structure was easier to understand (hence the use of Magmi), I wouldn't be having this issue. For now though, I need to use this. I just can't wrap my head around it at all and I just need a friendly push in the right direction...
I really would appreciate someones help here :)