database design

Question

i have 3 tables represnt cities, villages and districts related in 1 to many relation. then i have 23 tables represent services in each city, village and district. services like: education, electricity.... etc.

what is the best design for this database?

Answer 1

Hi,

it depends on your requirements (for example query performance, etc.). Without the requirements and all the tables it could be hard to give a good design, but a snowflake schema is the first which comes to my mind.

---

Best Regards. When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. This helps us build a healthy and positive community.

Answer 2

So if you add a new city you will have to create 24 th table for its services?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

You may want generalize both  cities, villages and districts as AdmUnits and education, electricity.... etc. as Services, move common columns to the  AdmUnits and Services, and create a table Servings for the facts that the Service serves the AdmUnit.

Or may not. It really depends.

---

Serg

Answer 4

"what is the best design for this database?"

What do you mean? What is the best design in continuing it? Or what is the best redesign?

Answer 5

i want to be able to select a city or a village or a district to show the services available in this place. what is "snowflake"? i'm not familiar with the term!

Answer 6

no, table like education should have rows related cities, villages and districts describing number of schools, number of classes, number of teachers... etc in this place. and so on for the rest of the tables.

Answer 7

cities, villages and districts tables need to be in heirarical structure bcause each city has some villages related to it and each village has some districts related to it. services need to be categorized for more redability and also all together the 23 tables conatins about 200 fields.

Answer 8

i'm open to any thought. maybe redesign. i don't mind.

Answer 9

Open? :)

Please delineate the entities and their relationships. A simple expanation should do.

Something like:

cities
villages (FK cities)
districts (FK villages)

education (FK city)
electricity (FK district)

Just the entities, and their relationships. Once we get an idea of your current model, we may be able to help.

Answer 10

For the cities, villages and districts hierarchy you may want to implement the HierarchyID datatype. More info can be found in BOL

---

John

http://knowledgy.org

Answer 11

For a 'city' data model, you may try something like this:

http://www.databaseanswers.org/data_models/city_councils/index.htm

Do the relationships make sense?

Answer 12

Always remember that database desing should be normalized but to a some extent we need to denormalize it inorder to achieve performance by letting redundant data.

Answer 13

Hi,

has your question been answered? If so, please mark the appropriate post as answer so other community members with a similar problem can find help faster.

---

Best Regards. When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. This helps us build a healthy and positive community.