none
database design

    質問

  • 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?

    2012年2月26日 20:54

回答

  • 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.


    2012年2月27日 22:02

すべての返信

  • 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.

    2012年2月26日 22:21
  • 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/

    2012年2月27日 5:45
  • 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

    2012年2月27日 7:03
  • "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?

    2012年2月27日 13:23
  • 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!
    2012年2月27日 21:42
  • 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.
    2012年2月27日 21:44
  • 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.
    2012年2月27日 21:47
  • i'm open to any thought. maybe redesign. i don't mind.
    2012年2月27日 21:48
  • 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.


    2012年2月27日 22:02
  • 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

    2012年2月27日 23:38
  • 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?

    2012年3月22日 16:19
  • 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.
    2012年3月23日 7:05
  • 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.

    2012年3月30日 2:23