sql structure

#wio-struct Module should be capable to store multiple data about each node in organization structure.

##Multiple structure trees We want to keep multiple structures (aka networks) in the same database ecosystem.
Not all the structures must be independent.
Some new structure trees could grow in the middle of old ones.
Some structures can share some of the nodes.

##Real world administrative structure added We have big needs to map structure of our organizations to the administrative structure of a country.
Organization structures and administrative structure trees will be linked for easier search.

##Almost-tree structures Our structures have the tree form. Or they so in 95% of cases. We want to treat them as trees but with possibility of making "hacks".
Some trees can split and then join again few levels lower or be linked to other items on the same level.

We are using graph theory model where we have nodes and links that connecting them.
Additionally we store node_flags as informations about regions. The informations can be different types as:

• is region claimed,
• by whom,
• is it working,
• is it sponsored,
• is it shown on different maps or lists
• etc.

We also have 3 valueObject-like tables.

wio_struct_type

This is first valueObject-like table.
It holds the names of structure networks in our system. It serves as reference to all other tables.
0 network is administrative structure of real world.
1-... networks are structures of our organizations.

CREATE TABLE `wio_struct_networks` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(64) NOT NULL ,
    PRIMARY KEY (`id`)
);

###who_struct_node_types This is second valueObject-like table.
Here we keep types of our nodes.
Node types are network related. Examples:

• Country - Administrative
• State - Administrative
• State - Organization 1
• Region - Administrative
• Region - Organization 2
• City - Administrative
• Area - Organization 1
• School - Administrative
• Collegium - Organization 2

Every node has one node_type

CREATE TABLE `wio_struct_node_types` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `network_id` INT(11) NOT NULL ,
    `name` VARCHAR(64) NOT NULL ,
    PRIMARY KEY (`id`)
);

###wio_struct_nodes One of main tables in database.
Here we hold tree nodes with some main informations about them.
For now we keep name of the node with GoogleMaps lat-lng geographical informations.
All informations that can easily change should be kept in node_flags table.

CREATE TABLE `wio_struct_nodes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `node_type_id` INT(11) NOT NULL ,
    `name` VARCHAR(64) NOT NULL ,
    `lat` FLOAT( 10, 6 ) NOT NULL ,
    `lng` FLOAT( 10, 6 ) NOT NULL ,
    PRIMARY KEY (`id`)
);

###wio_struct_links Another important table.
It holds all links between our nodes.
Two important columns are node_parent_id and node_children_id. They are creating the links.
As nodes form the tree-like structure the node_parent_id should be treated as closer to root than node_children_id. Its more like suggestion than strict rule in cases where tree rules are violated.

Note: If we have link between 2 nodes from 2 different networks that are on similar level we treat administrative node as closer to root than organization node.

###wio_struct_links - auto_generated As tree structures are not easy to search in, we want to generate all not direct links to the node.
Links will be created by our module and be marked with 1 in this column.
We want to generate all links to not direct ancestors of every node. The additional links should be made for inside network ancestors of node and for administrative tree ancestors of node.

CREATE TABLE `wio_struct_links` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `node_parent_id` INT(11) NOT NULL ,
    `node_children_id` INT(11) NOT NULL ,
    `auto_generated` INT(3) NOT NULL ,
    PRIMARY KEY (`id`)
);

###wio_struct_flag_types This is third valueObject-like table. It stores different flag types. It serves for wio_struct_node_flags reference.

CREATE TABLE `wio_struct_flags_types` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(64) NOT NULL ,
    PRIMARY KEY (`id`)
);

###wio_struct_flags This table keeps all flags of the node.
Every node can have multiple flags and they can be often changed.
Every flag has some flat_type and stores some data.
Stored data can be different types but it shouldn't have long informations in it.

CREATE TABLE `wio_struct_node_flags` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `node_id` INT (11) NOT NULL ,
    `flag_type_id` INT(11) NOT NULL ,
    `flag_data` VARCHAR(255) NOT NULL ,
    PRIMARY KEY (`id`)
);