Hierarchy of objects

Mattiman · #1 (**permalink**) 05-31-10, 07:52

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.

r937 · #2 (**permalink**) 05-31-10, 09:12

Quote:

Originally Posted by Mattiman

is this even possible?

yes, do it like that, with two optional foreign keys

Mattiman · #3 (**permalink**) 05-31-10, 10:41

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

r937 · #4 (**permalink**) 05-31-10, 10:58

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

Mattiman · #5 (**permalink**) 05-31-10, 11:50

Quote:

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

Pat Phelan · #6 (**permalink**) 05-31-10, 12:18

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

r937 · #7 (**permalink**) 05-31-10, 13:15

... an "N/A" value ???!!!

you will regret making that suggestion, sir

Mattiman · #8 (**permalink**) 05-31-10, 13:16

@pat: thanks for your input.

Quote:

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

Quote:

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;

r937 · #9 (**permalink**) 05-31-10, 13:19

see, pat, this is where it leads...

Quote:

`sector_id` INT(10) NOT NULL DEFAULT "n/a",

no, mattiman, you would not do it that way

Mattiman · #10 (**permalink**) 05-31-10, 14:31

Quote:

Originally Posted by r937

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.

Pat Phelan · #11 (**permalink**) 05-31-10, 15:45

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

Mattiman · #12 (**permalink**) 05-31-10, 16: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.

Pat Phelan · #13 (**permalink**) 05-31-10, 17:06

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

Mattiman · #14 (**permalink**) 06-01-10, 02:54

Quote:

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.

Pat Phelan · #15 (**permalink**) 06-01-10, 14:03

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

Internet Services

Web Development

Digital Marketing

Consumer Tech