PK for table that have not unique data

Question

I have 2 tables like

Company( #id_company, ... )

addresses( address, *id_company*, *id_city* )

cities( #id_city, name_city, *id_county* )

countries( #id_country, name_country )

What i want is :

It is a good design ? ( a company can have many addresses )

And the important thing is that you my notice that i didn't add a PK for addresses table because every address of a companies will be different, so am I right ?

And i will never have a where in a select that specify a address.

Answer 1

First of all we should distinguish natural keys and technical keys. As to natural keys:

1. A country is uniquely identified by its name.
2. A city can be uniquely identified by its country and a unique name. For instance there are two Frankfurt in Germany. To make sure what we are talking about we either use the distinct names Frankfurt/Main and Frankfurt/Oder or use the city name with its zip codes range.
3. A company gets identified by its full name usually. Or use some tax id, code, whatever.
4. To uniquely identify a company address we would take the company plus country, city and address in the city (street name and number usually).

You've decided to use technical keys. That's okay. But you should still make sure that names are unique. You don't want France and France in your table, it must be there just once. You don't want Frankfurt and Frankfurt without any distinction in your city table for Germany either. And you don't want to have the same address twice entered for one company.

• company( #id_company, name_company, ... ) plus a unique constraint on name_country or whatever makes a company unique
• countries( #id_country, name_country ) plus a unique constraint on name_country
• cities( #id_city, name_city, id_county ) plus a unique constraint on name_city, id_country
• addresses( address, id_company, id_city ) with a unique constraint on all three columns

From what you say, it looks like you want the addresses only for lookup. You don't want to use them in any other table, not now and not in the future. Well, then you are done. As you need a unique constraint on all three columns, you could just as well declare this as your primary key, but you don't have to.

Keep in mind, that to reference a company address in any other future table, you would have to store address + id_company + id_city in that table. At that point you would certainly like to have an address id instead. But you can add that when needed. For now you can do without.