CODEWITHSUNDEEP
mysqlsqlsql-server
Parry
Parry

Reputation: 187

Creating a table with two different foreign keys

I have to create a table with relationships to two different tables. Consider the example below.

Lets say I have following three tables:

1) Person -> person_id, residence_type (V or T)
2) Village -> village_id, village_name
3) Town -> town_id, town_name

Now what I want to do is create a foreign key reference in person table for the person's village or town. I thought of two ways.

One is to create one column of residence_name and just input id from village or town table and then query it based on residence_type whether its V or T. But surely I cannot create it as a foreign key reference for two different tables at same time.

Another option is to create two columns - v_id and t_id in person table and fill one based on residence_type. Again I can query it accordingly but surely cannot create it as foreign key reference as it cannot be NULL but in this case one column will always be NULL.

Which of the two approaches is better or is there any better approach which will allow me to create a foreign key reference?

Upvotes: 0

Views: 99

Answers (1)

Niels Berglund
Niels Berglund

Reputation: 1698

To me it looks like the design is somewhat "iffy". My assumption is that for a person you'd want to know what his residence is, i.e. ultimately name of village or town?

If that is the case, I'd remove the Village and Town tables and replace that with a Residence look up table which contains both Villages and towns, and which has a column to a ResidenceType table. The ResidenceType table would look something like so (this is SQL Server syntax):

CREATE TABLE dbo.ResidenceType
(
  ResidenceTypeID smallint NOT NULL,
  ResidenceTypeDefinition nvarchar(25),
  CONSTRAINT [pk_ResidenceType] PRIMARY KEY (ResidenceTypeID),
);

You would then populate that table like so:

INSERT INTO dbo.ResidenceType(ResidenceTypeID, ResidenceTypeDefinition)
VALUES (1, 'Village'), (2, 'Town');

Now you can create a Residence table, whoch would hold both town and village names - and it would have a foreign key against the ResidenceType table:

CREATE TABLE dbo.Residence
(
  ResidenceID int NOT NULL,
  ResidenceTypeID smallint NOT NULL,
  ResidenceName nvarchar(25),
  CONSTRAINT [pk_Residence] PRIMARY KEY (ResidenceID),
  CONSTRAINT [fk_ResidenceTypeID] FOREIGN KEY (ResidenceTypeID)
  REFERENCES dbo.ResidenceType(ResidenceTypeID),
);

Add some data:

INSERT INTO dbo.Residence(ResidenceID, ResidenceTypeID, ResidenceName)
VALUES (1, 1, 'Village 1'),
       (2, 1, 'Village 2'),
       (3, 2, 'Town 1'),
       etc...

Finally your Person table would look something like:

CREATE TABLE dbo.Person
(
  PersonID bigint NOT NULL,
  ResidenceID int NOT NULL,
  CONSTRAINT [pk_Person] PRIMARY KEY (PersonID),
  CONSTRAINT [fk_Person_ResidenceID] FOREIGN KEY (ResidenceID)
  REFERENCES dbo.Residence(ResidenceID),
);

That's the way I would suggest you'd do it. You are now following best practices when it comes to normalization etc.

Upvotes: 1

Related Questions