Building a CSV file to import into sql, how to handle relationships?
Question: I have a very large set of data where each row in table A point to a lot of
rows in table B. In generating a CSV file, I would need to somehow show this
relation, but I don't have an integer based pkey. Each of the items in A have
a unique username, and the same goes for table B (that is, table B has its own
unique set of names).

I am working with Python and Postgres if that matters. Another note, both
tables are 50-100M+ rows long, about 8 columns each.

Is there a good strategy for building out this CSV file?


Answer: You say each row has a unique name. If they truly are unique then you can use
them as keys for your tables. You don't need integer-based keys.

For example, the path to a file can be considered a unique identifier (for
files that are all in the same directory/repository.) So that could be the
file's key.

  * Advantages: Easier to import. All references to that file are human readable.
  * Disadvantages: each reference to that file uses more database space than an integer key. If a file is renamed it looks like a new file; the name can't change without changing the key. (Although, there are workarounds.)

The import will be much easier without assigning unique integer keys. I
suggest you import first, then optionally add integer keys afterwards.

Import: put the data in the CSV files, one file per table. Then import them
into the destination database in the correct order. (If I understand your
structure correctly the first one would be the Repository table, as Commits
and Changes both refer to it.)

Adding integer keys to existing tables: Add an autonumbering column to each
table that needs an integer key. So each parent row now has it's original
unique name and an integer ID. Then you can use SQL commands to replace each
parent name in a child table with it's respective internal key, then drop the
extra name columns once they are no longer needed.

