I have a flat file (CSV) that brings me the data I want, but it needs to be cleaned and processed before it can be used, so I built an intermediate Database (MySQL) in order to get the structure from flat file as I want and like I want (cleaned and formated)... so then I use that data and insert into a CMS.
This process is iterative so more and more flat files come and a script (PHP and stored procedures) will do the cleaning and saving.
What happens now is that both structures (my intermediate DB and the CMS's DB) are similar and I'm worried about the solution I'm using is not so good, and maybe there's better option (because it's like having the same tables in each DB).... For example:
In intermediate DB:
element: id_web, name, col1, col2, var
category: name, col3, var (probably modified so variable)
In CMS's DB:
cms_element: id_el, col, col, var
cms_element_lang: id_el, id_web (added/modified structure), name, col1
cms_category: id_cat, cols
cms_category_lang: id_cat, name, col3, var
cms_category_element: id_cat, id_el (use col2 from table: element to assign relationship)
As explained before, this process is iteratived so I use the id_web for elements to recognize the register and change only the variable field (var), and for categories I use just the name, I know it's not a good idea but is it to modified the CMS structure and add my own id?
I've been doing the process like this and now I want to make it better but I'm not sure how to do it.
In the case of the relation between both (element and category) is it ok to not save it in the intermediate DB, just on CMS's DB?
EDITED: I'm trying to use the intermediate DB as a repository with no dependencies on a specific CMS (or version of CMS).
And as @Xavier clarified me, I want this repository to be useful as a way to easy "replay".
