Table design for Country look-ups

Question

Post reply

Table design for Country look-ups

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
February 4, 2008 at 3:40 am

#154108

Hi All,
I have come across a database with an interesting design.
There seems to be for example.
tblCountry
as well as
tblPassportCountry
tblOriginCountry
tblDestinationCountry
The tblCountry table holds all countries being used by any system
The other three tables only hold rows used for looks etc that are for those categories.
My question is this:
Would it not be better to have another column say for example an INT that holds a Bitwise reference to where that country can be referenced?
Would this not save duplicate info in the database as well as reduce the number of tables being used to 1 instead of 3?
Thanking you in advance.
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi All,

Does anyone have any feedback on this for me?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Alexander G. SSCrazy Points: 2705 More actions · Answer 2

As often the answer is: It depends.

The solution you suggested might be easier but also less flexible. Looking at the logical model this is the straight way to realize a generalization (i. e. inheritance) relationship between two classes:

Let's say you have a person with a name either Italian or Japanese.

You could define a table

Person

-------

id

name

italian_flag

japanese_flag (or just some nationality identifer)

But what if you have to assign nationality specific attributes to the person, like the favorite pasta for the Italian and the favorite sushi for the Japanese (assuming Italians don't like sushi 😉

To add those attributes to the class person might leed to attributes that are filled only for very few persons. In such a case it might make sense to look at the generalization:

Person

-------

id

name

Italian (is a / inherits from Person)

-------

favorite_pasta

Japanese (is a / inherits from Person)

---------

favorite_sushi

"Italian" and "Japanese" is also called a specialization of "person". It can make sense to realize these additional tables with foreign key constraints (person_id) to the person table. You have to balance flexibility, reasonable programming effort, redundancy (i. e. data quality). And in case to voting for flexibility: You should be quite sure, whether one attribute is really specific to one specialization. Often you have orthogonal inheritances like Italian vs. Japanese, male vs. female. What if you have one property that is specific to male italians and one that is specific to females?

You can see, it's a really complex topic very open to argument.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Thanks for the reply 🙂

The one thing that concerns me is that for our 3 country tables at the moment they all have the same number of rows referencing the same countries in the MainCountry table!

So it would be like having pasta on you japanese table as well.

To me this is duplicate data or am I looking at it in the wrong way?

I guess another way to do it would be to create 3 views based on the countrytable to filter for passport desitination etc.

That why we don't have to create extra tables, is this a bad solution?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Alexander G. SSCrazy Points: 2705 More actions · Answer 4

The way you describe it I don't have a clue either. Maybe it is a question of competence and authorization then (even if there are better ways to solve this)...

If all the rows are the same probably all could well fit into one table...

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

http://www.datamodel.org/NormalizationRules.html

One table Countries

CountryID CountryName

1 USA

2 Sweden

...

Another table

CountryLinks

CountryID LinkType

1 1

1 2

2 1

3 1

3 2

3 3

Where LinkType denotes which type of link that is relevant (much like your bit approach), meaning passport, origin and destination.

N 56°04'39.16"
E 12°55'05.25"

Alexander G. SSCrazy Points: 2705 More actions · Answer 6

You should ask whether it a link table is necessary at all. Maybe there exists some business rule that states that each country has all of the linkage types.