CODEWITHSUNDEEP
mysqldatabase-design
Frank Fotangs
Frank Fotangs

Reputation: 50

Database Dependencies - Database Design

I'm designing a database with the following attribute dependencies: Approach 1: A <- B <- C <-D Approach 2: A <- B, {A,B} < C, {A,B,C} <- D;

Please I need your help on which approach is better. Thanks

EDIT

Sample tables for approach 1

Country_info ------------- , state info, city_info, village_info

id | country_id | name

TABLE PAIRS

country_state

id | state_id | country_id

state_division

id | division_id | state_id

village_division

id | village_id | division_id

Now, I have the id of a village and I want to know the name of the country in which it belongs. I will have to look for the division, state before arriving at the country.

With the second approach, the village table will have the division_id, state_id and the country_id.

Thanks!

Upvotes: 0

Views: 52

Answers (2)

Rick James
Rick James

Reputation: 142218

KISS.

Table 1: A business/person/etc has an address and a City.

Table 2: The City also includes the Viliage, State, Province, Country_code, Postal_code, whatever.

Normalizing each layer is overkill.

If you have half a dozen tables, imagine the number of JOINs needed to get all the parts of the address!

Upvotes: 1

Kamil Kiełczewski
Kamil Kiełczewski

Reputation: 92347

If village is "main" obiect which will be used very often (and it's relations to other tables will be also often used) then by using second approach you will reduce number of code lines and increase performance (eg. in filtering villages by country).

Upvotes: 1

Related Questions