0

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.

Nikunj Madhogaria
  • 1,931
  • 2
  • 21
  • 38
  • Your first design violates [3NF](http://en.wikipedia.org/wiki/Third_normal_form). The second design is normal. – eggyal Apr 05 '15 at 06:44
  • So, can I go with my second design? Or are there any better alternatives? – Nikunj Madhogaria Apr 05 '15 at 06:46
  • You may like to read the following related questions: [Best practices for storing postal addresses in a database (RDBMS)?](http://stackoverflow.com/q/310540) [Is there common street addresses database design for all addresses of the world?](http://stackoverflow.com/q/929684) [How should international geographical addresses be stored in a relational database?](http://stackoverflow.com/q/1159756) [What is the “best” way to store international addresses in a database?](http://stackoverflow.com/q/24481) – eggyal Apr 05 '15 at 08:19
  • @eggyal, the relationships described in the question are just an example of one of the possible cases. What I need to know is whether it's appropriate to define relationships between the Master and Transaction tables like I've done in the above example. Thanks for the links though. :) – Nikunj Madhogaria Apr 05 '15 at 10:02
  • 2
    A good general rule of thumb is to "*normalise until it hurts; then denormalise until it works*". On that basis, option #2 is the best option unless/until you encounter problems that require an alternative approach. – eggyal Apr 05 '15 at 13:39

1 Answers1

0

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.

Rick James
  • 122,779
  • 10
  • 116
  • 195