[FIXED] normalize PostgreSQL database

Issue

I have a PostgresQL database that I populate programmatically in my Java application code. There is a table "Message" - it stores incoming messages from the mail service. And also "Phone" - which stores phone numbers. The UUID for each entity is automatically generated. The problem is that if the message can contain the same number. And in the database, due to auto-generation, phone_id is always different (see photo). How to avoid this?

Solution

While this answer is not about normalization, I hope it helps. In case you want to link a UUID number to a phone number, you can also generate UUID v3 or UUID v5 instead of UUIDv4. A UUIDv4 is random, while the UUID v3/v5 is always the same for the same input.

To generate a UUIDv3:

package com.example;

import java.util.UUID;

public class Example {
    
    public static void main(String[] args) {
        String string1 = "89207143040";
        String string2 = "8 920 714 30 40";
        String string3 = "+8 920 714-30-40";
        
        // Remove all non numeric chars from the phone number and generate a UUIDs v3.
        UUID uuid1 = UUID.nameUUIDFromBytes(string1.replaceAll("[^\\d.]", "").getBytes());
        UUID uuid2 = UUID.nameUUIDFromBytes(string2.replaceAll("[^\\d.]", "").getBytes());
        UUID uuid3 = UUID.nameUUIDFromBytes(string3.replaceAll("[^\\d.]", "").getBytes());
        
        System.out.println(uuid1 + " -> " + string1);
        System.out.println(uuid2 + " -> " + string2);
        System.out.println(uuid3 + " -> " + string3);
    }
}

OUTPUT:

84345d67-45a8-365e-8da7-5d5c90c1ce0c -> 89207143040
84345d67-45a8-365e-8da7-5d5c90c1ce0c -> 8 920 714 30 40
84345d67-45a8-365e-8da7-5d5c90c1ce0c -> +8 920 714-30-40

Answered By - fabiolimace
Answer Checked By - Cary Denson (JavaFixing Admin)