var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
best design for states, countries and customers
i always wanted to know what is the best database design to link customers, countries and states tables/entities.
several countries have states (e.g. USA)
if a customer is located in 1 country, he can (or not) live in a state also.
therefore how are those 3 entities related to each other.
- if table "countries" has a primary key "country_id"
- if table "states" has a primary key "state_id"
as we know a state belongs to only 1 country, therefore in table "states", we should have a foreign key redirecting to table "countries" (countries_id - primary key).
now a customer is located in a country (and maybe in a state).
therefore in table "customers" we should have 2 foreign keys (states_id and countries_id)
but in this case all three tables have recycling relationships
any idea how this situation should be solved ?
You need an adjacency table (recursive) for geographic units.
ID int not null
ParentID int null (Recursive foreign key to GeographicUnit.ID)
UnitType ("State", "Country", "Province", etc...)
Then you can assign a location at any level.