lundi 6 juillet 2015

SQL: Design pattern for integrating data with different data types and/or data lengths

Let's say I have data in 3 different tables which have different Primary Key column names and data types, here are the table definitions:

CREATE TABLE dbo.Table1
(
    Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    AddressLine1 VARCHAR(100) NULL,
    AddressLine2 VARCHAR(100) NULL,
    AddressLine3 VARCHAR(100) NULL,
    AddressLine4 VARCHAR(100) NULL,
    Postcode VARCHAR(10)
)
GO

CREATE TABLE dbo.Table2
(
    UniqueID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
    AddressLine1 VARCHAR(100) NULL,
    AddressLine2 VARCHAR(100) NULL,
    AddressLine3 VARCHAR(100) NULL,
    AddressLine4 VARCHAR(100) NULL,
    Postcode VARCHAR(10)
)
GO

CREATE TABLE dbo.Table3
(
    Reference VARCHAR(10) PRIMARY KEY NOT NULL,
    AddressLine1 VARCHAR(100) NULL,
    AddressLine2 VARCHAR(100) NULL,
    AddressLine3 VARCHAR(100) NULL,
    AddressLine4 VARCHAR(100) NULL,
    Postcode VARCHAR(10)
)

The above 3 tables are independent of each other. As we can see, the primary key columns' names and data types are different in all of the tables.

What I need to do is integrate the data from the 3 tables above into just one table to carry out a specific process, but I need to later on link the information back to the source tables, so I was thinking of creating a table like this:

CREATE TABLE dbo.DataIntegrated
(
    SourceTableName sysname,
    SourceTablePK VARCHAR(100), 
    AddressLine1 VARCHAR(100) NULL,
    AddressLine2 VARCHAR(100) NULL,
    AddressLine3 VARCHAR(100) NULL,
    AddressLine4 VARCHAR(100) NULL,
    Postcode VARCHAR(10),
    CONSTRAINT PK_DataIntegrated PRIMARY KEY CLUSTERED
    (SourceTableName, SourceTablePK)
)

The above table would allow me to integrate data form all of the 3 tables, but given the different Primary Key column data types in Table1, Table2 and Table3 (INT, UNIQUEIDENTIFIER and VARCHAR(10), respectively) I am having to use a VARCHAR column in dbo.DataIntegrated table in order to store the Primary Key value from each source table, which does not look very elegant and can be risky I suppose? Is there a better way of doing this?

Aucun commentaire:

Enregistrer un commentaire