How to define relationships between tables from differnt sources?

Question

I need to perform a complete overhaul of two major systems. Taking a BI perspective, I created some baseline tables, "User", "CRM Details", "Telephony Details" etc.

I have an issue with understanding how relationships are formed between tables.

I dropped a list of users into the "Users" table, and I'll need to do the same into the "Telephony Details" table. How do I build a relationship where it knows 'John Smith' from Users = 'John Smith' in Telephony?

I anticipate the key columns will be "Id" in the Users table and therefore "UserId" in the Telephony table, but how does the UserId end up in the Telephony table?

Code I have so far:

CREATE TABLE Users
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Forename  NVARCHAR(50),
    Surname   NVARCHAR(50),
    Location  CHAR(50),
    Email     NVARCHAR(320),
    SCD_Start SMALLDATETIME,
    SCD_Stop  SMALLDATETIME,
    IsActive  BIT
)

INSERT INTO Users (Forename,Surname,Location,SCD_Start,SCD_Stop,IsActive)
VALUES ('Test1','Test1','TestL1','2016-11-08',NULL,1)
,('Test2','Test2','TestL2','2016-11-08',NULL,1)
,('Test3','Test3','TestL3','2016-11-08',NULL,1)
,('Test4','Test4','TestL4','2016-11-08',NULL,1)
,('Test5','Test5','TestL5','2016-11-08',NULL,1)
,('Test6','Test6','TestL6','2016-11-08',NULL,1)

CREATE TABLE Telephony
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Forename    NVARCHAR(50),
    Surname     NVARCHAR(50),
    OfficePhone VARCHAR(22),
    MobilePhone VARCHAR(22),
    SCD_Start   SMALLDATETIME,
    SCD_Stop    SMALLDATETIME,
    IsActive    BIT
)

Answer 1

For this particular case, you need to introduce a foreign key in Telephony table referencing Id in Users table.

CREATE TABLE Telephony
        (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        UserId int FOREIGN KEY REFERENCES Users(Id)
        Forename    NVARCHAR(50),
        Surname     NVARCHAR(50),
        OfficePhone VARCHAR(22),
        MobilePhone VARCHAR(22),
        SCD_Start   SMALLDATETIME,
        SCD_Stop    SMALLDATETIME,
        IsActive    BIT
)

In general, if you're more familiar with designing software than designing database, it might be a good idea to dig into domain modeling and code first -principles.

Answer 2

Basically, just add another column to Telephony that matches the type (INT) of the Id in Users - call it maybe UserId (often I'll prefix it with "f", fUserId just so a quick glance reminds me it's and FK and not some other value). Then run this command:

alter table Telephony with check add constraint FK_Telephony_UserId foreign key(UserId)
references Users (Id)

Now any records added to Telephony must have a matching UserId. Query using the Users Id value and you'll only get their numbers.

That's it.

Answer 3

Identification by Name

Presumably in both tables you have

PRIMARY KEY (forename, surname);

You have to decide when a name in a row of Users represents the same person as some row in Telephony. Maybe it's

Users.forename = Telephony.forename AND Users.surname = Telephony.forename

This is a data cleansing/cleaning/scrubbing issue. Anyway, let's say there's a condition ....

A table represents a relationship. If it's a base or metadata table, the DBA/designer tells you the relationship. If it's the result of a query, the query tells you the relationship.

So when you have a query involving two tables and the rows in the tables refer to people by name and you want the people named by two rows to refer to the same people then you add the requirement ... to the query.

Adding ids

We assign unique ids to refer to entities for many reasons. Eg because you don't want to change the many places a name otherwise appears when a person changes their name. Eg so that your queries can involve only an equality on ids instead of multiple columns. Eg so that by using a smaller (integer) identifier rather than a larger (varchar) one you can decrease implementation space (in tables and indexing) and time (in comparisons and indexing) at the expense of increasing implementation space (more tables) and time (more joins). Eg so you can associate many names with the same person. Note that there are tradeoffs for any design decision.

Assuming you want ids: First per SQL Server: how to add new identity column and populate column with ids? add an id column to each table "and the column will be created and automatically populated". If you were starting with tables with no ids:

ALTER TABLE Users ADD Id INT IDENTITY(1,1) PRIMARY KEY
ALTER TABLE Telephony ADD Id INT IDENTITY(1,1) PRIMARY KEY

Then per Update a table using JOIN in SQL Server? set each Telephony id to the User id with the same name per "..." above:

UPDATE t
SET t.Id = u.Id
FROM Users as u
INNER JOIN Telephony as t
WHERE ...

Now you don't need or want names in Telephony:

DROP FORENAME, SURNAME FROM Telephony

Declaring foreign keys

"Relationship" (between tables) is also used for "foreign key constraint". You want a FK from Telephony to Users. You might want ids. But constraints are not needed to query. (The metadata has a table for relationship "there is a fk from table TF column list LF to table TT column list LT", also expressible by "list of values for table TF column list LF appear as lists of values for table TT column list LT".) inal Telephony,

FOREIGN KEY (forename, surname) REFERENCES Users (forename, surname);

For the new tables,

FOREIGN KEY (Id) REFERENCES Users (Id)

PS

A constraint is not actually a relationship, it is an instance of metadata relationship, ie one case of some values that participate in that metadata relationship. It states a fact about the values in base tables. But simultaneously, because each base table holds the rows satisfying some application relationship, it states a fact about every application situation in terms of the application relationships of base tables that it mentions. When table T represents a relationship expressed as E_T, FK constraint tf (c) REFERENCES tt (c) says, "for some values for all other columns, E_tf implies E_tt".