I have been searching through posts that discuss choosing hierarchical content from the database, however i have doubts how I possibly could implement the next: I have three tables:

  • Locations: Here' store an area and it is correspondent parent (e.g Europe->UK->London).
  • Structures: Here' store structures. One building should be in a.
  • User-locations: Here' keep locations where a specific user will have the ability to see.
  • User-Structures: Here' keep structures that the particular user will have the ability to see.

Observe that an admin know: "User 1 might find all of the structures from Japan, as well as he'll have the ability to observe that particular building from France, but simply that certain".

With all of that, I must develop a menu (for the user) that'll be something similar to:

  • Structures
    • Europe
      • Germany
        • Munich
          • Building_1
          • Building_2
      • United kingdom
        • London
          • Building_3

I am getting some doubts about how exactly could I recieve that information, and that i would appreciate any ideas.

Edited:

Table Structure:

1) Structures

  • Id (primary key)
  • Title, description,...
  • location_ID (foreign answer to location table)
  • Company_ID (foreign answer to the organization table.. A building must fit in with only one company).

2) Locations

  • Id (primary key)
  • Title
  • Parent Id (foreign answer to the table itself.. e.g: London entry might have an overseas answer to United kingdom entry.

3) Company

  • Id (primary key)
  • Title, description...

4) Customers

  • Id (primary key)
  • Company_ID (foreign answer to the organization of the user (a person goes simply to one company).

5) User-Structures

  • Id_User (Foreign answer to user table)
  • Id_Building (Foreign answer to building table)

6) User-Location

  • Id_User (Foreign answer to user table)
  • Id_Location (Foreign answer to location table)

The concept is the fact that a business can assign which structures a person can easily see. A business can assign specific structures (not location dependent) which explains why the presence of the consumer-Building table, plus they may also assign an area, therefore the user might find all of the structures owed to that particular location.

Just comments, and that i hope I have clarified a bit.

Thanks.

If you wish to stick to your present database schema, however , locations don't appear to possess a fixed quantity of levels, so you'll have to do an ugly loop to fetch sublevels of user_location.id_location as well as their structures. It might be very convenient should you could distinguish location levels in significant groups for example region, country, city. This way you might have this type of table definition:

CREATE TABLE buildings (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    ...
    region_id INTEGER UNSIGNED NOT NULL,
    country_id INTEGER UNSIGNED NOT NULL,
    city_id INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    INDEX (region_id),
    INDEX (country_id),
    INDEX (city_id),
    FOREIGN KEY (region_id) REFERENCES locations(id) ON DELETE RESTRICT,
    FOREIGN KEY (country_id) REFERENCES locations(id) ON DELETE RESTRICT,
    FOREIGN KEY (city_id) REFERENCES locations(id) ON DELETE RESTRICT
) TYPE=InnoDB;

CREATE TABLE user_location (
    user_id INTEGER UNSIGNED NOT NULL,
    location_id INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, region_id, country_id, city_id),
    INDEX (user_id),
    INDEX (location_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE
) TYPE=InnoDB;

You'll be able to retrieve your structures such as this:

SELECT * FROM buildings WHERE id IN (
    (SELECT building_id FROM user_buildings WHERE user_id = ?)
    UNION
    (SELECT id FROM buildings 
    LEFT JOIN user_location ON 
    (user_location.location_id = buildings.region_id
        OR user_location.location_id = buildings.country_id
        OR user_location.location_id = buildings.city_id)
    WHERE user_id = ?)
);

Came back rows will retain the important information to construct your tree.