normalize PostgreSQL database-CodePudding

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?

CodePudding user response：

One practice is to use constraint on the phone_id field to avoid the duplicate entity with same phone_number.

In the case you are using database-independent library for managing the databases like Liquibase you can achieve it by

<changeSet  author="liquibase-docs"  id="dropUniqueConstraint-example">  
<dropUniqueConstraint  catalogName="cat"  
        constraintName="const_name"  
        schemaName="your schma name"  
        tableName="Message"  
        uniqueColumns="phone_number"/>

Or directly in database with

ALTER TABLE Message ADD CONSTRAINT constraint_name UNIQUE (phone_number);

An other approach for satisfying 2NF is to create alternate table which foreign key pointing to phome_number in Message table with One to Many association or Many to Many if they have bidirectional relation .

CodePudding user response：

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