Thread by @vincentntang

TIL there's lots of ways of #seeding #fakedata into a #SQL #database. You can either

A. Seed it with purely random data all the way
B. Seed it with realistic data
C. Seed it initially with random data, but tie that data into other tables realistically

When you seed a database, you start off with enum (that's like a dictionary that doesn't change in size). If you look at a database schema, it's the tables that are only connected one time.

Then you focus on tables that have only have 1 foreign key tied, tied to an enum

Example with a basic HR database schema. Start seeding the outlier tables first, starting with the bottom-left most and bottom-right most tables, followed by bottom-middle.

For workflow A, you would use the following workflow:

1. Use a library like faker.js or make your own string randomizer using Math.random()
2. Generate data from step 1.
3. Associate all the foreign keys at random using Math.random*arraylength

For workflow B, do following:

1. Do same thing as steps A
2. Initially create random data for a few tables
3. But have some data created based off that random data

For instance, say we have two tables. A franchisor table and a franchisee table.

Generate a random list of companies. Say one of those companies is named McDonalds.

The franchisee table data should say `McDonalds Store 100` or `McDonalds Store 101` tied to McDonalds as a FK

For workflow C to get very realistic data, do following:

1. Generate a google spreadsheet
2. Copy all the columns in your database over. One table = one sheet in google spreadsheet
3. Generate a list of fake data using something like mockaroo.com

4. Tie the data together using foreign key mappings of some lookup name.
5. Export all your sheets in google spreadsheet, using a tool like csvjson.com/csv2json
6. Paste this into your repo. If you use an ORM like #sequelize, load this in and run the seed command

7. Do error checking with your scripts. Sometimes when you make a google spreadsheet worth of data, you'll run into human mistypo errors
8. Sometimes you'll have constraints in your database that requires nonprimary-keys to be unique

Last note

When you update your database schema, the amount of effort for rewriting your code looks like this:

Workflow A (pure random data) is very easy
Workflow B (pure random data to start) is not as bad
Workflow C (realistic data) requires a lot of rewrites.

Last Note 2

Pure .SQL files might have other tools and methods you can use, but I'm running this through a #sequelize ORM in #NodeJS