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?

    יום ראשון 26 פברואר 2012 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.


    יום שני 27 פברואר 2012 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.

    יום ראשון 26 פברואר 2012 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/

    יום שני 27 פברואר 2012 05: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

    יום שני 27 פברואר 2012 07: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?

    יום שני 27 פברואר 2012 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!
    יום שני 27 פברואר 2012 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.
    יום שני 27 פברואר 2012 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.
    יום שני 27 פברואר 2012 21:47
  • i'm open to any thought. maybe redesign. i don't mind.
    יום שני 27 פברואר 2012 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.


    יום שני 27 פברואר 2012 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

    יום שני 27 פברואר 2012 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?

    יום חמישי 22 מרץ 2012 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.
    יום שישי 23 מרץ 2012 07: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.

    יום שישי 30 מרץ 2012 02:23