1

I desgined a database relationship between users, countries, states and cities. I think is not a good desgin and may be improved because is has circular references

Which would be a good way to redesign this?

enter image description here

UPDATE

With the answers I arrived to a new design

enter image description here

agusgambina
  • 6,229
  • 14
  • 54
  • 94
  • make it linear? users -> cities -> states -> countries – lusketeer May 21 '16 at 17:38
  • @lusketeer, thank you for your comment. I thought about that, but in the case that the user does not live in an state or city, I will loose the country for him. – agusgambina May 21 '16 at 17:40
  • The update is very similar to the 1st option in my "if you're trying to do both" section. That's certainly a valid approach, but you'd probably want to enforce via code somewhere that exactly 1 of locations.country_id, locations.state_id and locations.city_id is non-null (and the others are null). This would avoid the possibility of inconsistent data by pointing to e.g. a city that's in a different country to the one pointed to by country_id. Alternatively, you have to update all of them at once (by copying from the countries -> cities tables) or not at all. – Bob Salmon May 22 '16 at 08:15

3 Answers3

1

In your database design, the relation Country - State - Cities is not necessary . It Is redundant. So, my suggestion is delete the atributes country_id Table STATE and state_id Table CITIES.

UPDATE:

If your design is well done and there is good cause to guarantee you mention circular reference, then there is no problem to leave such a reference.

I assumed you had a circular reference exception but after reading @reaanb post, I agree with him.

  • Thank for the answer @FernandaInesDuran but what would happen then is if I have 3 select combos in a web page, and when the user selects a country the second combo should show only the states of that country I need that relationship. – agusgambina May 21 '16 at 18:14
  • Ok, in this situation you need to create other table with only 2 keys: IdCountry and IdState. (Table Master CountryState) What do u think? In other case, you will have circular reference. Try this and tell me later. –  May 21 '16 at 18:26
  • thank you for your comment, with the Bob answer and your comment, I updated the design. What do you think about the new one? Thank you – agusgambina May 22 '16 at 02:24
  • 1
    The new model is not right for me. Look: For example, in the table locations you might have this values: id 1 country 1 state 1 city 1. But then the table city you might have: id 1 name 'mycity' country 99. Inconsistency of data. –  May 22 '16 at 11:52
  • 1
    The circular reference is not a problem if the relational data model is well done, but be sure that the field that produces the circularity accept NULL to stop at some point references. –  May 22 '16 at 12:02
1

I'm trying to work out what you're trying to achieve. I can imagine 2 things, but it could be something else.

The first is that you're trying to just represent geographic data (cities contained in states, contained in countries). A user points to a city, which then links to a state and then a country.

The second is that you allow the user to give their address at any of the 3 levels of precision. I.e. they could say which city they live in, or just which state, or even just which country.

If you're trying to do just the first one, then you should get rid of the user -> state and user -> country relationships, and link user to just city. If you need to know which state and/or country a user is in, just join from city to state and/or country.

If you're trying to do the second one, then it looks quite a lot like polymorphic association, so I suggest you read the linked post and see which you think works best for you.

If you're trying to do both, then I think you have 2 options. The first is to keep the schema as it is, but enforce via business logic that exactly one of the address fields on user is non-null. This would avoid the possible problem that a user could point to e.g. a state and a city that is in a different state.

The second is to make it explicit that you've got 3 types of address (all levels, just country+state, just country). This is more expensive in terms of tables, but makes it clearer what's going on i.e. constraints aren't hidden away in business logic.

For the second option you'd have something like this:

Users
@user_id
(remove address fields)

Country
@country_id
(other fields)

State
@state_id
country_id (foreign key)

City
@city_id
state_id (foreign key)

L1_Address
@l1_address_id
country_id (foreign key)
address_id (foreign key)

L2_Address
@l2_address_id
state_id (foreign key)
address_id (foreign key)

L3_Address
@l3_address_id
city_id (foreign key)
address_id (foreign key)

UserAddress
@user_Id
@address_id

Community
  • 1
  • 1
Bob Salmon
  • 411
  • 4
  • 10
  • thank you for your answer. It was really good, the problem I am facing is both situations but at the same time. For the first situation It may happen that the user lives in a farm, so he does not have a city, but he has state and country. On the other hand I need the locations entities related for querying the database before showing the options to the users. – agusgambina May 22 '16 at 02:23
  • thank you for your answer, as now is clear for me, the options are what you have said. Between both I prefer the first one (enforce bussiness logic) because I think the origin of the problem is related with the impedance mismatch, and I prefer to keep both worlds the less entangled as possible. – agusgambina May 22 '16 at 14:33
1

You don't have any circular references. The functional dependencies in your first diagram (excluding names) are:

user_id -> city_id
user_id -> state_id
user_id -> country_id
city_id -> state_id
state_id -> country_id

The problem is that this set of FDs is redundant since relations compose. For example, user_id -> city_id and city_id -> state_id implies a transitive dependency user_id -> state_id, which you record explicitly as well. Thus, there's two paths from user_id to state_id, and three from user_id to country_id. This is understandable since you want to support variable levels of detail.

Redundant data is a risk to data integrity, so if we can mitigate that risk, redundancy won't be a problem. Depending on your DBMS, you may be able to enforce composite foreign key constraints that include nulls, i.e. let (city_id, state_id) in users reference (city_id, state_id) in cities and let (state_id, country_id) in users reference (state_id, country_id) in states. Alternatively, your DBMS may support check constraints.

reaanb
  • 9,806
  • 2
  • 23
  • 37