CODEWITHSUNDEEP
sqldatabase-designprimary-keyprimary-key-design
DotnetShadow
DotnetShadow

Reputation: 778

Database design: Primary key from multiple data sources

Initially I was importing foods from an excel document from Food Source 1 and it had VARCHAR type primary*(PK example #FOOD0001)* (Because there was only 1 source at the time I just imported directly into the foods table with auto incrementing int ID)

But I have a need to import food from another source Food Source 2 which has completely primary key type (INT) (PK example #25928747)

I currently have:

Foods table

INT FoodId<PK>, Name

Servings table

INT ServingId<PK>, FoodId<FK>, Name, Size

What is the best database design so that any food source could be imported that won't affect the current ids or at least has a mapping so that foods can easily be updated, deleted etc? I don't want to change the ID to a VARCHAR for performance reasons

One idea I have is to introduce a FoodSourceFoodId in my foods table that has the original id from the food source, that way if a food gets changed/updated from the food source then it can easily be updated in the foods table?

Foods table 

INT FoodId<PK>, *VARCHAR FoodSourceFoodId*, Name
      1               #FOOD0001             Food 1
      2               #FOOD0002             Food 2
      3               25928747              Food 1
      4               25928748              Food 2

Similarly I could do the same thing to the servings table where the serving id might relate to the serving id in the source data

Do you think this is the way to go? Or would you suggest something else?

Upvotes: 2

Views: 619

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

I would recommend against modelling values from two distinct types (domains) in the same column, especially when the types in question map to distinct SQL data types.

Suggestion: use a 'subtype' table, including their respective 'natural' key, for each source and a single 'supertype' table to consolidate them using your artifical key FoodId e.g. 

CREATE TABLE Foods
(
 FoodId INTEGER NOT NULL UNIQUE, 
 Name CHAR(6) NOT NULL 
    CHECK (Name IN ('Food 1', 'Food 2')), 
 UNIQUE (Name, FoodId)
);

CREATE TABLE Foods1
(
 FoodId INTEGER NOT NULL UNIQUE, 
 Name CHAR(6) NOT NULL 
    CHECK (Name = 'Food 1'), 
 FOREIGN KEY (Name, FoodId)
    REFERENCES Foods (Name, FoodId)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 Food1_ID CHAR(9) NOT NULL UNIQUE 
    CHECK (Food1_ID LIKE '#FOOD[0-9][0-9][0-9][0-9]')
);

CREATE TABLE Foods2
(
 FoodId INTEGER NOT NULL UNIQUE, 
 Name CHAR(6) NOT NULL 
    CHECK (Name = 'Food 2'), 
 FOREIGN KEY (Name, FoodId)
    REFERENCES Foods (Name, FoodId)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 Food2_ID INTEGER NOT NULL UNIQUE
);

Upvotes: 2

Hogan
Hogan

Reputation: 70523

This seems like a fine plan. Another more common option would be to make a support table with INT FoodID as a foreign key (to the foods table) and VARCHAR ID. Then when you no longer need to support the imports you can just toss the table.

Upvotes: 1

Related Questions