Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Question about modeling abstract relationships

    What I'm trying to do is create a database that stores geographic locations. It could be a restaurant, shop, city, zipcode, state, province or country. For each geographic location, I want to be able to provide some type of description and even rating or review. I would like to model this in a database, but I don't want to have to explicitly state exactly how many layers of relationships there are. For example, in the US we have states, but in other countries there aren't any. So I don't want to make a country table that links to a state table that links to a city table because if the country has no states, then how am I supposed to get to the city table.

    What I was thinking was creating one table where each geographic location will have a foreign key relationship with another record in the same table which would be the parent for that location (for example, US would be the parent for Colorado). So by doing this, you can keep adding relationships and drilling down further and further without limitations imposed by the database table structure. It seems like this isn't a very good idea though, but I don't really know why. Does anyone have any suggestions about tackling a problem like this?
    Paul Palubinski

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think this is better suited to Database Concepts and Design. Most posters there frequent here too, but there are some that don't use SQL Server. Let me know if you are happy for a move.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ppalubinski
    It seems like this isn't a very good idea though...
    ah, but it is

    see Categories and Subcategories for sample queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by pootle flump
    I think this is better suited to Database Concepts and Design. Most posters there frequent here too, but there are some that don't use SQL Server. Let me know if you are happy for a move.
    Oh, sorry. I usually just post here because most of my issues are SQL Server related, so I didn't even think about checking for a more relevant discussion area. I'd definitely be happy with a move. Thanks.
    Paul Palubinski

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •