best structure of my table in Perfomance way:

i have two different database schemas

First Structure is :

table 1 :    country_master
Column name: id(primary key)   country_name    

table 2:     State_master
Column name: id(primary key) State_name Country_id(foreign key of country_master)

table 3:     City_Master
Column name: id(primary key) city_name State_id(foreign key of State_master)

Second Structure is:

table 1 :    Master_table
Column name: id(primary key)   name    

table 2 :    Sub_master
Column name: id(primary key) name master_id(Foreign key of master Table) subid(id of Sub_master)

Record of first structure

table: Country_master
1  india
2  UK
3  USA


table:State_master
1 gujarat 1
2 MP      1
3 Up      1

table:City_master
1 ahmedabad 1
2 surat     1

Record of structure 2:

table:master_table
1 Country
2 City
3 State

table:master_table
1  india      1 0
2  UK         1 0
3  USA        1 0
4  Gujarat    3 1
5 MP          3 1
6 Up          3 1
7 ahmedabad   2 4 
8 surat       2 4

now my question is what is best schema of my tables for performance:

Answers


The second structure gonna be create more issue regarding performance point of view. As the child table have all references, it is difficult to maintain. Consider the scenario, where you need to mark for the "capital" of each state having the crores of data, then what would you do to add one more column into existing table and update each of one? No not obviously, instead the better job is to separate all the cities, states and countries in a separate table and use in query having joins, so join performes on primary key. And the most important is, these data not gonna be change often means these remains constant most of the times. These are called lookups, hence you need to separate in a table. The first structure is good in a first appearance because dataset is too small, as data increases, that gonna create problems, hence the better is to use the first structure.


Need Your Help

Some MVVM questions (WPF C#)

c# wpf mvvm algorithm

I have been looking into MVVM recently and I seem to get the overall idea. There are a couple of niggly bits though that I do not fully understand and was hopping to get some answers here, cheers!

How to do Tabs using jquery-ui, haml & sinatra?

jquery-ui sinatra haml

I have sinatra, jquery-ui and haml. I want to replicate http://jqueryui.com/tabs/#default in haml.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.