Joint foreign key between index field and a possible string

Question

I am creating an application where I thought of a possibility to insert all existing addresses in the system in a single table, both for users, customers, suppliers etc. the structure of my table then would look like this:

__id => CHAVE_PRIMÁRIA
__status
* idReferencia => CHAVE_EXTRANGEIRA_CONJUNTA (REF. GENÉRICA COM BASE NO txTabela)
* txTabela  => CHAVE_EXTRANGEIRA_CONJUNTA (STRING: 'CLIENTE','FORNECEDOR',etc)
txGenero
nmCep
txLogradouro
txComplemento
txBairro
txLocalidade
txUf
txIbge

Is there any way I can mount this foreign key using an existing string in the field txTabela? or will I have to keep these addresses on numerous different tables and significantly increase the size of my database without need? (Bs, this would also occur with contacts, documents and other types of generic values)

I am sure that if I do not use foreign keys I will be able to build the structure of my system completely, but with the keys the data loading improves..

Answer 1

In your place, I would make an N to N association with a CHECK CONSTRAINT to check that only one of the Fks is filled:

CREATE TABLE ENDERECO 
(
    ENDERECO_ID INT PRIMARY KEY IDENTITY,
    STATUS VARCHAR(10),
    GENERO VARCHAR(10),
    CEP VARCHAR(8)
    LOGRADOURO VARCHAR(500),
    COMPLEMENTO VARCHAR(500),
    BAIRRO VARCHAR(100),
    LOCALIDADE VARCHAR(255),
    UF CHAR(2),
    IBGE NUMERIC(10)
);

CREATE TABLE ENDERECO_RELACOES (
    ENDERECO_RELACAO_ID INT PRIMARY KEY IDENTITY,
    ENDERECO_ID INT,
    CLIENTE_ID INT NULL,
    FORNECEDOR_ID INT NULL
    OUTRA_TABELA_ID INT NULL,
    CONSTRAINT ENDERECO_REL_ENDERECO_FK FOREIGN KEY (ENDERECO_ID) REFERENCES ENDERECO (ENDERECO_ID),
    CONSTRAINT ENDERECO_REL_FORNECEDOR_FK FOREIGN KEY (FORNECEDOR_ID) REFERENCES FORNECEDOR (FORNECEDOR_ID),
    CONSTRAINT ENDERECO_REL_CLIENTE_FK FOREIGN KEY (CLIENTE) REFERENCES CLIENTE (CLIENTE_ID),
    CONSTRAINT ENDERECO_REL_OUTRA_TABELA_FK FOREIGN KEY (OUTRA_TABELA_ID) REFERENCES OUTRA_TABELA (OUTRA_TABELA_ID)
);

ALTER TABLE ENDERECO_RELACOES 
ADD CONSTRAINT CheckFKs
CHECK (
(CASE WHEN CLIENTE_ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN FORNECEDOR_ID IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN OUTRA_TABELA_ID IS NOT NULL THEN 1 ELSE 0 END)
    = 1
);