Arrays in SQL? Yay or Nay?

66np — Tue, 06 Dec 2022 23:21:28 +0000

Photo by Naim Benjelloun

Disclaimer: Coming from a dev perspective, NOT DBA.

I'm visiting this topic for an nth time, and decided to illustrate use cases (with and without), and come up with a pros/cons list.

Problem: In a database for a social networking platform, suppose I need to organize the data for a user's friends.

This can be done in 1 of 2 ways:

1. Within a singular table ('user profile')

| id | name | friends_id |
--------------------------
| 1  | jess | {3, 4}     |
| 2  | mike |            |
| 3  | raj  | {1}        |
| 4  | sam  | {1}        |

2. Split into two tables ('user_profile', 'user_friends')

| id | name |
-------------
| 1  | jess |
| 2  | mike |
| 3  | raj  |
| 4  | sam  |

| id | user_id | friend_id |
----------------------------
| 1  | 1       | 3         |
| 2  | 1       | 4         |
| 3  | 3       | 1         |
| 4  | 4       | 1         |

PROS:

Spatial locality allows us to minimize the number of query calls to the DB which can be a bit of performance booster (i.e. INSERT INTO user_profile (name, friends_id) VALUES ('jess', ARRAY[3, 4]); VS. INSERT INTO user_profile (name) VALUES ('jess'); INSERT INTO user_friends (user_id, friend_id) VALUES (1, 3); INSERT INTO user_friends (user_id, friend_id) VALUES (1, 4);)
Reduces data redundancy (i.e. the multiple records for user id 1 under table 'user_friends')
Can be especially useful when processing large amounts of data (i.e. PostGIS point data, ML datasets)

CONS:

Breaks database normalization rules like 1NF (First Normal Form) being a multi-valued attribute which introduces CRUD anomalies (i.e. UPDATE user_profile SET friends_id = array_append(friends_id, 2) WHERE id = 1; VS. INSERT user_friends (user_id, friend_id) VALUES (1, 2);)
Breaks database normalization rules like introducing null values where the data can be normalized (i.e. User with id 2 in the first example has 0 friends, consequently nulling the friends_id column. This could've been prevented by introducing the second table 'user_friends'.)
Does not allow for modularity or new information per entry within the array (What if I wanted to date when each friendship was initiated through the app? Order the data based on this new attribute?)

| id | name |
-------------
| 1  | jess |
| 2  | mike |
| 3  | raj  |
| 4  | sam  |

| id | user_id | friend_id | created_at |
-----------------------------------------
| 1  | 1       | 3         | 2020-09-02 |
| 2  | 1       | 4         | 2020-03-20 | 
| 3  | 3       | 1         | 2020-09-02 |
| 4  | 4       | 1         | 2020-03-20 |

Not a lot of SQL Engines and ORMS are optimized to handle arrays and lists (i.e. from experience, the schema file generated by Diesel ORM for Rust tends to mess up on optional array fields, as well as reported issues on migrations to MySQL/SQLite)

What are your thoughts?

How To Publish A Static React/Node.js App Using cPanel (the easy way)

66np — Tue, 22 Jun 2021 01:09:31 +0000

I've been stuck on this problem recently and finally figured it out a week in. Except the solution surely took less than ten minutes so I decided to share the wisdom.

Pro-tip: Forget the Setup Node.js App feature on your cPanel. Instead, you want to focus your attention on the build folder. When you run yarn run build, you are effectively generating static files to be used in production.

Once I made all my changes locally, I used the following steps to deploy my site:

On your local machine

1) In your package.json, add the line "homepage":"<yourdomain>".

2) In your VSCode terminal, run yarn run build.

You should see something like this:

The project was built assuming it is hosted at <yourdomain>.

If you're seeing '/' or anything else in place of your domain, run export PUBLIC_URL=<yourdomain> first, then run yarn run build again. This time, you should see your domain.

3) Go into the directory /<yourproject>/build/, highlight all the files and folders, right-click and navigate to Send to > Compressed (zipped) folder to generate a zipped folder.

On your cPanel

4) Go into the directory /home/<yourusername>/<yourdomain>/ and Upload the zipped folder. Then, extract its contents like this:

Remember to delete the .zip folder, and voila! You're done!

A few notes to keep in mind here:

For some, it may take a while to load under your actual domain or in the same browser with all that cPanel activity. I used Incognito mode and other devices to check visit my domain and see the updates.
I have not tried this with a full stack application (keyword: static) as of yet. Though I will be confronting it in the near future so stay tuned!

DEV Community: 66np

Arrays in SQL? Yay or Nay?

How To Publish A Static React/Node.js App Using cPanel (the easy way)