0

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".

-1

Do you need a separate step? It's often faster to read each line, clean it and insert it into your CMS.

If you have a lot of cleaning and processing, you might want to look at kettle from pentaho, it handle Extraction, Transformation, and Loading (ETL) and could help you (beware, takes a while to master)

3
  • Well I'm new to this and I don't know how to deal with this... but I'm trying to use the intermediate DB as a repository with no dependencies on a specific CMS (or version of CMS) – Gerardo Charles Rojas Vega Oct 30 '14 at 1:15
  • 1
    Are you ever going to import this csv into different CMSes? The main practical benefit of a separate table is that it's easier to "replay". ie you import, realise there is something wrong, delete every record and retry. If you need a proper more abstract tool because you want to be able to use it with different CMSes, then don't use anything from the target CMS. – Xavier Oct 30 '14 at 12:30
  • Yes, what I want is to use this repository as a way to "replay" (I would like to give you a +1 for your comment but it seems I can't). Thanks for clarify me that. – Gerardo Charles Rojas Vega Oct 30 '14 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.