CODEWITHSUNDEEP
sql
user3340627
user3340627

Reputation: 3143

Is this a good database design practice?

I got a Person table, each Person can visit several countries. The countries visited by each Person is stored in table CountryVisit

Person:

PersonId,
Name

CountryVisit:

CountryVisitId (primary key)
PersonId (foreign key to 'Person.PersonId')
CountryName
VisitDate

For the CountryVisit Table, my primary key is CountryVisitId which is an identity column. This design will result in that a Person can have only 1 CountryVisit but the CountryVisitId can be 40 for example. Is it a better practice to create another surrogate key column to act as an identity column while the CountryVisitId be a natural key that is unique for each PersonId ?

Upvotes: 1

Views: 108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

It is pretty good. I would suggest that you have a separate table for countries, with one row per country. Then the CountryVisits table would have:

CountryVisitId PrimaryKey,
PersonId ForeignKey, 
CountryId ForeignKey,
VisitDate

This will ensure that the country name is always spelled correctly and consistently. If you want a list of countries to get started, check out this Wikipedia page. Also note that your definition of country may be different from the standard list of countries (there are actually several out there), so you should use your own auto-incremented primary key, rather than using the country code.

And, you should relax the requirement and remove the unique or primary key on PersonId, CountryId, unless you want to enforce only one visit per country.

Upvotes: 8

Related Questions