Foreign key joint between index field and a possible string

Question

Foreign key joint between index field and a possible string

Navigation

#1 by (1 votes)

2

I'm creating an application where I thought of a possibility to insert all the existing addresses in the system into a single table, for users, clients, suppliers, etc. the structure of my table 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 txTabela field? or will I have to keep these addresses in countless different tables and significantly increasing the size of my database without need? (obs, this would also happen with contacts, documents and other types of generic values)

I'm sure if I do not use foreign keys I'll be able to build my system structure completely, but with the keys the data loading improves ..

sql sql-server

asked by anonymous 23.01.2015 / 20:53

1 answer

VideoView overlaying another videoView on TabActivity Android Multiple config files on different projects ASPNET MVC

score 1 · Accepted Answer

In its place, I would make an association N for N with a CHECK CONSTRAINT to check if 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
);