Relationships between Master and Transaction tables

In my database design, I've defined Master tables (data definition tables, static in nature) which will be used to generate content in my web page; and Transaction tables which will be used to store data entered by users (these tables are dynamic in nature).

Consider the following example:

Set of Master tables consisting of State having 1:M relationship with City, City having 1:M relationship with Locality.

A Transaction table User to store the personal details entered by a user. The User table has address attributes like Address, State, City and Locality. These attributes can be defined as 1:M relationships from the corresponding Master Tables (a particular record in State, City, Locality tables can be a part of more than records in User table).

enter image description here

My questions:

  • Is the above design correct?
  • Moreover, I think it's sufficient to define 1:M relationship between the Locality and the User tables since the other two attributes (City and State) can be obtained from the relationships between the Master tables. Would it be better to change the ER design to the following?enter image description here
  • Are there any good alternatives to my requirement?

PS: I'm a beginner in database designing.


What queries do you have? Do you ever need to search by state or city? Even if you do search by those, it may not impact what I am about to say...

Since locality, city, and state are 'nested' and it is not likely for the names to change, I suggest that both of your options are "over-normalized". One table with all three items in it is the way I would go.

As I see it, there are two main reasons for normalizing:

  • Locating some string that is likely to change. By putting it in a separate table and pointing to that table, you can change it on only one place. This is not needed in your example.
  • Saving space (hence providing speed, etc). This does apply in your example, but only at the locality level, not at address. You might argue that city and state can be dedupped; I would counter with "The added complexity (extra tables) does not warrant the minimal benefit.".

A side note: If locality is zipcode, then your option 1 is in trouble at least one place I know of: Los Altos and Los Altos Hills (two different cities in California) both have sections of zipcodes 94022 and 94024.