Is a 1-to-1 relationship appropriate for this scenario?

Question

I was wondering if a one-to-one relationship is appropriate for the following scenario:

I have a lookup table consisting of two columns, ID and TrackingNumber, which maintains a list of tracking numbers (phone numbers used for forwarding). We also have an existing table containing our client's phone numbers.

We would like to occasionally track certain phone numbers by linking them to a tracking number. When this happens, a start and end date for the tracking is determined. Once the end date has passed we would like that row to stay in the table, but be marked as inactive. The reason for this is that if that customer requires tracking again in the future, we would like to re-use the same tracking number if possible.

To begin with the TrackingNumber table will be populated, but the RefTrackingNumber table won't. We may also add further entries into the TrackingNumber table in future. I realise that one option is to just have the one table, but that would require removing the NOT NULL constraints to begin with.

The best solution I can come up with is the following the example for this post: Defining a one-to-one relationship in SQL Server

Is there a better way?

Thanks.

CREATE TABLE TrackingNumber (
    ID int PRIMARY KEY,
    TrackingNumber varchar(20)
)


CREATE TABLE RefTrackingNumber (
    ID int PRIMARY KEY,
    RefPhoneNumber int NOT NULL,
    StartDate datetime NOT NULL,
    EndDate datetime NOT NULL,
    Active bit NOT NULL
)

ALTER TABLE RefTrackingNumber
    ADD FOREIGN KEY (ID) REFERENCES TrackingNumber(ID)

Answer 1

This is a 1:M relationship from the database's perspective. Your logic for inactivating records has no meaning to an RDBMS. Unless of course you plan to move them to a history table, but judging from your StartDate and EndDate you won't be.

You'll probably want to create a surrogate key for RefTrackingNumber, and then change your ID field in RefTrackingNumber to TrackingNumberID. It would look like this:

CREATE TABLE TrackingNumber (
    ID int PRIMARY KEY,
    TrackingNumber varchar(20)
)


CREATE TABLE RefTrackingNumber (
    ID int IDENTITY(1, 1) PRIMARY KEY,
    TrackingNumberID int NOT NULL,
    RefPhoneNumber int NOT NULL,
    StartDate datetime NOT NULL,
    EndDate datetime NOT NULL,
    Active bit NOT NULL
)

ALTER TABLE RefTrackingNumber
    ADD FOREIGN KEY (TrackingNumberID) REFERENCES TrackingNumber(ID)

You could create a VIEW to work with the current reference numbers:

CREATE VIEW CurrentRefTrackingNumber AS
SELECT ID, TrackingNumberID, RefPhoneNumber
FROM RefTrackingNumber
WHERE GETDATE() BETWEEN StartDate AND EndDate
  AND Active = 1;