Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2008
    Posts
    25

    Hierarchy of objects

    A user creates records of routes he climbed by filling in a form. For example, on 12-02-2009 I climbed the Route "Someroute", which can be found in the sector "Al Capitan" in the Area "Yosemite" which can be found in the US. So the record is:
    User: me
    Date: 12-02-2009
    Route: "Someroute"
    Area: "Yosemite"
    Sector: "Al Capitan"
    Country: "USA"

    Normalizing this, you get 6 tables, in some kind of hierarchy (each country has more areas, each area can have more sectors, each sector can have more routes, each route can have multiple climbs by the same or different users)

    Code:
    Users
    id
    name
    [more fields..]
    
    Climbs
    id
    date
    user_id  (FK constraint to id in Users)
    route_id (FK constraint to id in Routes)
    [more fields..]
    
    Routes
    id
    name
    sector_id (FK constraint to id in Sectors)
    [more fields..]
    
    Sectors
    id
    name
    area_id (FK constraint to id in Areas)
    
    Areas
    id
    name
    country_id (FK constraint to id in Countries)
    
    Countries
    id
    name

    However, I have the problem that the sector is optional. So not all climbing areas will have a subdivision in sectors. Sometimes you just have the climbing area "Bigwall" with a couple of routes in them, no sectors. So sometimes a user will fill in the form
    User: me
    Date: 12-02-2009
    Route: "Someroute"
    Area: "Yosemite"
    Sector: -
    Country: "USA"
    But the foreign key restraints don't allow this, since the Route has a foreign key to Sector, which has a foreign key to area.

    How do I solve this? Should I move the sector name in the Route table?

    Code:
    Climbs
    id
    date
    user_id  (FK constraint to id in Users)
    route_id (FK constraint to id in Routes)
    [more fields..]
    
    Routes
    id
    name
    sector_name 
    area_id  (FK constraint to id in Areas)
    [more fields..]
    
    Areas
    id
    name
    country_id (FK constraint to id in Countries)
    
    Countries
    id
    name
    But now the tables are not fully normalized. Maybe not very important, since the "sector" information is not very important in the application, but only a nice little piece of extra info. I could also do:

    Code:
    Climbs
    id
    date
    user_id  (FK constraint to id in Users)
    route_id (FK constraint to id in Routes)
    [more fields..]
    
    Routes
    id
    name
    sector_id  (optional, FK constraint to id in Sectors)
    area_id  (FK constraint to id in Areas)
    [more fields..]
    
    Sectors
    id
    name
    area_id (FK constraint to id in Areas)
    
    Areas
    id
    name
    country_id (FK constraint to id in Countries)
    
    Countries
    id
    name
    is this even possible? That a Route has two foreign keys, one to the sector table and one to the Area table, and the sector in the Sectors table at the same time has a constraint to the Areas table.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mattiman View Post
    is this even possible?
    yes, do it like that, with two optional foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    25
    Ok thanks. If you say "with two optional foreign keys", you mean

    Code:
    Routes
    id
    name
    sector_id  (optional, FK constraint to id in Sectors)
    area_id  (FK constraint to id in Areas)
    [more fields..]
    
    Sectors
    id
    name
    area_id (optional, FK constraint to id in Areas)
    
    Areas
    id
    name
    country_id (FK constraint to id in Countries)
    ?

    Thanks again for the help

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in the routes table, if the route belongs to a sector, then the area_id should be NULL (because the sector will link to the area), but if the route belongs to an area, then the sector_id will be NULL

    thus, both columns are optional
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2008
    Posts
    25
    in the routes table, if the route belongs to a sector, then the area_id should be NULL (because the sector will link to the area), but if the route belongs to an area, then the sector_id will be NULL
    ok please help me clarify this.

    Should I enforce this in the PHP logic? Meaning, should I program my PHP so that
    1) in case both a sector and area are filled in,
    - the area is added to the Areas table
    - the sector+area_id are added to the Sectors table
    - the route+sector_id are added to the Routes table
    2) in case only an area is filled in
    - the area is added to the Areas table
    - the route+area_id are added to the Routes table

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    A lot depends on your logic used to process the hierarchy.

    If the sector is truly optional, I'd make it a NULLable Foreign Key to the area. This allows you to store and process a sector as an optional "super group" of areas.

    If there is ever logically a need to process the sector, then you need to always have a sector and make the area a non-NULLable Foreign Key. For areas that don't have sectors (yet!), include a sector anyway as a "N/A" value.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ... an "N/A" value ???!!!

    you will regret making that suggestion, sir
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2008
    Posts
    25
    @pat: thanks for your input.
    If the sector is truly optional, I'd make it a NULLable Foreign Key to the area. This allows you to store and process a sector as an optional "super group" of areas.
    Ok I'll try this

    If there is ever logically a need to process the sector, then you need to always have a sector and make the area a non-NULLable Foreign Key. For areas that don't have sectors (yet!), include a sector anyway as a "N/A" value.
    With "N/A" I assume you mean something like Non Applicable? (sorry, English is not my native language). And what do you mean with "if there's ever logically a need to process the sector"?
    Getting a list of all possible sectors out of the db, for example?

    So what you mean is that if there is no sector filled in, I have a default value of n/a. So my table description becomes
    Code:
    CREATE TABLE `mc_routes` (
      	`route_id` int(10) unsigned NOT NULL auto_increment,
      	`route_name` varchar(255) NOT NULL,
      	`route_grade` enum('3','4','5a','5a+','5b','5b+','5c','5c+','6a','6a+','6b','6b+','6c','6c+','7a','7a+','7b','7b+','7c','7c+','8a','8a+','8b','8b+','8c','8c+','9a','9a+','9b','9b+') NOT NULL,
    	`sector_id` INT(10) NOT NULL DEFAULT "n/a",
      	`area_id` int(10) unsigned NOT NULL,
      	PRIMARY KEY  (`route_id`),
    	UNIQUE KEY (`route_name`, `route_grade`, `area_id`),
      	FOREIGN KEY (`area_id`) REFERENCES `mc_areas` (`area_id`),
      	FOREIGN KEY (`sector_id`) REFERENCES `mc_sectors` (`sector_id`),
    )  ENGINE=InnoDB;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see, pat, this is where it leads...
    `sector_id` INT(10) NOT NULL DEFAULT "n/a",
    no, mattiman, you would not do it that way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2008
    Posts
    25
    Quote Originally Posted by r937 View Post
    see, pat, this is where it leads...

    no, mattiman, you would not do it that way
    Am I allowed to ask why? My guess is that it could be better to do:
    Code:
    `sector_id` INT(10) NULL
    because NULL is cleaner then "n/a". With NULL it would be clear that it is "not filled in", not having a value, real null.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Before we take off on a tangent that might not be productive, you need to answer a question about your application... How does "sector" fit into your hierarchy? In your first post you described sector information as not being important to your application. In post #8, it seems like sector information is very important and possibly critical to your application. How does sector really figure into your application?

    My first guess is that sector is truly optional and that it ought to be a NULLable Foreign Key in your Area entity.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Aug 2008
    Posts
    25
    I can describe the app model a bit more. Climbers are the users. They use the app to keep track of what they climbed. So those are the routes they climbed. Every route in the world can be found in a certain climbing area. For example, Yosemite is such a climbing area. Or Red River Gorge. Often, such a climbing area has a guide describing all the routes in the area. An area can be very small, containing only a dozen routes. Or it can be very big, containing thousands of routes. If the area is bigger, often the area will be divided/contains different sectors. So in Yosemite you have the Al Capitan, or Half Dome. But, as I said, not all areas will have sectors.

    Now when a climber keeps track of the routes he climbed, he will want to keep track of the names of the route, the grade and the area the route is in. But possibly also the sector.

    So the sector field is not essential, but not being able to keep track of the sector would be a miss.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Based on that description, then the Sector couldn't be used for processing because it is truly optional. The Area could be used for processing, because it is required. The Sector describes the Area, so it would be an optional attribute (a NULLable Foreign Key) of the Area.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Aug 2008
    Posts
    25
    Based on that description, then the Sector couldn't be used for processing because it is truly optional. The Area could be used for processing, because it is required. The Sector describes the Area, so it would be an optional attribute (a NULLable Foreign Key) of the Area.
    This is confusing. Either I don't understand what you mean or you didn't understand what I tried to explain about relationship sectors-areas

    A single climbing area can have more sectors within it. A single sector does not have more areas. So a sector is not an attribute of the area. It's the other way around. An area is an attribute of a sector.

    It's like this:

    USA
    - Yosemite (=area)
    -- Al Capitan (=sector)
    --- Big chimney route, grade 5.10 (=route)
    --- The monkey way, grade 5.11 (=route)
    --- etc etc
    -- Half Dome (=sector)
    --- Zodiac, grade 5.12 (=route)
    --- Sparrow, grade 5.10 (=route)
    - Bishop (=area)
    -- Buttermilks (=sector)
    --- Mandela, grade 5.14 (=route)
    --- etc
    -- Happy Boulders (=sector)
    --- big roof, grade 5.11 (=route)

    Belgium
    - Bomal (=area)
    -- Golden line, grade 5.11 (=route)
    -- Big chimney, grade 5.10 (=route)

    Now as you see, the Area Bomal is smaller then Yosemite or Bishop, and does not have different sectors.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Sorry, I appear to have gotten confused by the terminology. I think that my understanding was fine, but I had the Foos right and was calling the Bats as Bars. I've cleaned up the phrasology below, so see if it makes more sense to you now.

    All of your hierarchies (specifically the Areas) have Routes, but not all of the Areas have Sectors. I would make the Area a required (non-NULLable) Foreign Key of the Route, and the Sector an option (NULLable) Foreign Key of the Route. This means that any given route might or might not have a Sector, but all Routes will have an Area.

    Does this make more sense?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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