General Database Structure Query

[Mark12345]

HI,

I am starting the process of building a database to export one CSV file from various different suppliers. My suppliers are all providing their data in different ways and with different fields. Some will have bullet points and description, some will have tags, others not, etc.

During the course of a day, I will import data from up to 10 different suppliers, sometimes up to 3 times per day. 

I need to treat each supplier's data differently to get the same unified output CSV file from each supplier, in fact ideally 1 large combined, unified output CSV file for all suppliers.

What is the best way to structure such a database? Should I have a table for each supplier and then export the data from each table? If I do it this way, is there a way to output 1 CSV file covering all the suppliers together?

The database is effectively acting as an 'interpreter' between each supplier and the output CSV file and it doesn't do much more than this,. Does it make sense to somehow import all the different data files into 1 single table? My only concern with this method is that each supplier's data requires different treatment to output the final file and I assume that would be easier to achieve if each supplier's data was imported into it's own table within the database.

If anyone can help or anything requires clarification, please let me know.

Thanks in advance.

 

[comment]

5 minutes ago, Mark12345 said:

each supplier's data requires different treatment to output the final file

I don't think we can answer your question without knowing what type of "treatment" is required here. Perhaps all you need is a separate import script for each supplier, channeling their data into common fields. Or, if the data needs to re-combined using calculations, have a set of input fields for each supplier and a set of output calculation fields that pull the data from the various input fields.

 

 

[Ocean West]

Greetings Mark

I have had this issue in the past where i had to have several different supplier tables, and would periodically have to review the raw file to see if they without warning add, remove or change columns. In the approach to ingest data from multiple sources each new source may require schema table and fields - makes it much easier to deal with keeping focus on one at a time - a consolidated table you are always fighting what happens when some but not all column headers match, then you have other things to contend with some suppliers may send an amount as $50 some may send it as a raw number. Also each supplier may format dates differently. In either case you may need to have calculated fields or a scripted post import process to tidy up the data.

In some cases the suppliers columns may have illegal characters and thus may never match a fieldName. 

other approaches i've taken have been to injest the raw file as text in to a single text field then process thru each row and pivoting out the columns based on a known delimiter (Tab / PIPE / etc)  and as its processing put data in to respected columns in a dedicated table.

Depending on the complexity you could even employ a SQL plugin that can, based on input alter your schema per supplier. 

Other outside the box approaches could be to preflight raw data using AWK or other terminal text processing commands checking to see if data is properly formatted prior to using FMP for import. With some diligence you could even using this method alter the data file to add a column and row data or alter column or row data prior to importing. 

Other ideas that could be employed could be to merge data into a single output table such as virtual list to output the desired columns, or possibly iterate thru the data and write the data to a file for output. Even using the DataAPI to push a JSON payload to create necessary sanitized records that match your desired format.