I have a general question regarding the method of how to fill a database for the first time. Actually, I work on "raw" datasets within R (dataframes that I've built to work and give insights quickly) but I now need to structure and load everything in a relational Database.

For the DB design, everything is OK (=> Conceptual, logical and 3NF). The result is a quite "complex" (it's all relative) data model with many junction tables and foreign keys within tables.

My question is : Now, what is the easiest way for me to populate this DB ?

My approach would be to generate a .csv for each table starting from my "raw" dataframes in R and then load them table per table in the DB. Is it the good way to do it or do you have any easier method ? . Another point is, how to not struggle with FK constraints while populating ?

Thank you very much for the answers. I realize it's very "methodological" questions but I can't find any tutorial/thread related

Notes : I work with R (dplyr, etc.) and MySQL

A serious relational database, such as Postgres for example, will offer features for populating a large database.

Bulk loading

Look for commands that read in external data to be loaded into a table with a matching field structure. The data moves directly from the OS’s file system file directly into the table. This is vastly faster than loading individual rows with the usual SQL INSERT. Such commands are not standardized, so you must look for the proprietary commands in your particular database engine.

In Postgres that would be the COPY command.

Temporarily disabling referential-integrity

Look for commands that defer enforcing the foreign key relationship rules until after the data is loaded.

In Postgres, use SET CONSTRAINTS … DEFERRED to not check constraints during each statement, and instead wait until the end of the transaction.

Alternatively, if your database lacks such a feature, as part of your mass import routine, you could delete your constraints before and then re-establish them after. But beware, this may affect all other transactions in all other database connections. If you know the database has no other users, then perhaps this is workable.

Other issues

For other issues to consider, see the Populating a Database in the Postgres documentation (whether you use Postgres or not).

• Disable Autocommit


• Use COPY (for mass import, mentioned above)


• Remove Indexes


• Remove Foreign Key Constraints (mentioned above)


• Increase maintenance_work_mem (changing the memory allocation of your database engine)


• Increase max_wal_size (changing the configuration of your database engine’s write-ahead log)


• Disable WAL Archival and Streaming Replication (consider moving a copy of your database to replicant server(s) rather than letting replication move the mass data)


• Run ANALYZE Afterwards (remind your database engine to survey the new state of the data, for use by its query planner)

Database migration

By the way, you will likely find a database migration tool helpful in creating the tables and columns, and possibly in loading the data. Consider tools such as Flyway or Liquibase.