sql - PK for table that have not unique data -

sql - PK for table that have not unique data -

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 want :

it design ? ( company can have many addresses )

and of import thing notice didn't add together pk addresses table because every address of companies different, right ?

and never have where in select specify address.

first of should distinguish natural keys , technical keys. natural keys:

a country uniquely identified name. a city can uniquely identified country , unique name. instance there 2 frankfurt in germany. create sure talking either utilize distinct names frankfurt/main , frankfurt/oder or utilize city name zip codes range. a company gets identified total name usually. or utilize taxation id, code, whatever. to uniquely identify company address take company plus country, city , address in city (street name , number usually).

you've decided utilize technical keys. that's okay. should still create sure names unique. don't want french republic , french republic in table, must there once. don't want frankfurt , frankfurt without distinction in city table federal republic of germany either. , don't want have same address twice entered 1 company.

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

from say, looks want addresses lookup. don't want utilize them in other table, not , not in future. well, done. need unique constraint on 3 columns, declare primary key, don't have to.

keep in mind, reference company address in other future table, have store address + id_company + id_city in table. @ point have address id instead. can add together when needed. can without.

sql database database-design database-performance