Network Database Structure
The above diagram is a visual representaion of the structure of the network database. Each oval represents a table within the database. The lines represent the relationships that the tables have with each other. For example, the table patch_panel_port has a column called hub_port. If that column contains a value for a particular row, that value must apear in the table hub_port. The table hub_port has a field hub which must be filled and the value that fills that field must be present in the hub table.
This structure forces the entry of good data. It is impossible to enter data of the wrong type into the database because you can only choose data that is know to be valid. When users fill out the web form they will be given a list of values to choose from instead of being able to enter whatever they want. Good data is ensured and we can run varous searches to find out information about our network.
Below is a description of each table: (NN=not null, VS=value set, FK=foreign key)
| patch_panel | |||
| column name | type | length | constraints |
| * patch_panel_name | char | 20 | NN |
| building | char | 15 | VS, NN |
| patch_panel_port_count | int | VS, NN | |
| plug_type | char | 10 | VS, NN |
| patch_panel_brand | char | 15 | VS, NN |
| patch_panel_port | (when used) | ||
| column name | type | length | constraints |
| * patch_panel_name | char | 20 | FK, NN |
| * patch_panel_port_number | int | FK, NN | |
| host_name | char | 15 | FK, NN |
| hub_name | char | 15 | FK, NN |
| hub_port_number | int | VS, NN |
| hub | |||
| column name | type | length | constraints |
| * hub_name | char | 15 | NN |
| building | char | 15 | VS, NN |
| hub_port_count | int | VS, NN | |
| ip_address | char | 15 | |
| hub_brand | char | 15 | VS, NN |
| hub_port | (when used) | ||
| column name | type | length | constraints |
| * hub_name | char | 20 | FK, NN |
| * hub_port_number | int | FK, NN | |
| connection_speed | char | 10 | VS, NN |
| connection_type | char | 10 | VS, NN |
| user | |||
| column name | type | length | constraints |
| * employee_number | int | NN | |
| user_name | char | 15 | NN |
| first_name | char | 15 | NN |
| last_name | char | 15 | NN |
| host | |||
| column name | type | length | constraints |
| * host_name | char | 15 | NN |
| user_name | char | 15 | FK, NN |
| connection_type | char | 10 | VS, NN |
| connection_speed | char | 10 | VS, NN |
| computer_type | char | 10 | VS, NN |
| subnet | char | 15 | VS, NN |
| hardware_address | char | 20 | NN |
| ip_address | char | 15 | NN |