JRA1 D1.4 Database Schema

From GEANT2-JRA1 Wiki

Contents 1 Visualization 2 Common Database Schema 2.1 history - Generic Versioned Object Table 2.2 Topology tables (OSI layer 3) 2.2.1 monitoring_objects - Monitoring object table (inherits from generic versioned object table) 2.2.2 router - Node table (inherits from monitoring_object) 2.2.3 router_type - Router type table 2.2.4 monitoring_station - Monitoring station table (inherits from monitoring_objects) 2.2.5 Monitoring station Monitoring object link table (inherits from generic versioned object table) 2.2.6 metricdef_link - Monitoring station Metric definition link table (inherits from generic versioned object table) 2.2.7 interface - Interface table (inherits from monitoring_objects) 2.2.8 interface_ip_address - Connection table (inherits from versioned object table) 2.2.9 network - Subnet table (inherits from monitoring object table) 2.2.10 prefix - IP prefix table (inherits from generic versioned object table) 2.2.11 group - Monitoring object grouping table (inherits from monitoring object table) 2.2.12 group_consist_of - Monitoring object grouping element table (inherits from generic versioned object table) 2.3 Map tables 2.3.1 map - Map table (inherits from generic versioned object table) 2.3.2 geographical_map - Geographical map table 2.3.3 edge_coordinates - Map edge coordinate table (inherits from generic versioned object table) 2.3.4 map_element - Map element table (inherits from generic versioned object table) 2.3.5 map_node - Map node table (inherits from map element) 2.3.6 has_position - Map node Has position table (inherits from generic versioned object table) 2.3.7 action - Map element action table (inherits from generic versioned object table) 2.3.8 map_resolution - Map resolution 2.3.9 map_link - Map link table (inherits from map element) 2.3.10 Connected map node-to-link table (inherits from generic versioned object table) 2.4 Metric tables 2.4.1 Metric definition table 2.4.2 (obsolete: Metric scheduling table (replaced by Metric methodology table) ) 2.4.3 Tables for dealing with Metric methodology 2.4.3.1 Metric methodology template table 2.4.3.2 Metric methodology parameter table 2.4.3.3 Metric methodology template time resolution table 2.4.4 Tables dealing with measurement data 2.4.4.1 Measurement ID table 2.4.4.2 Measurement data directory table 2.4.4.3 Measurement subject table 2.4.4.4 Measurement single subject table (inherits from measurement subject table) 2.4.4.5 Measurement data table (generic (meta data about contained in measurement data directory table), could contain different metrics if polled at same time interval) 2.4.5 Tables dealing with aggregated measurement data 2.4.5.1 Measurement aggregated data tables (generic (meta data about contained in measurement data directory table), could contain different metrics if polled at same time interval) 2.5 Metric table example 2.5.1 Metric definition table examples 2.5.2 Metric methodology template table examples 2.5.3 Metric methodology parameterlist table examples 2.5.4 Metric methodology template time resolution table examples 2.5.5 Metric methodology time period table examples 2.5.6 Measurement ID table examples 2.5.7 Measurement data directory table examples 2.5.8 Measurement data table examples 2.6 Administrative tables 2.6.1 Domain table 2.6.2 User table 2.6.3 User group table 2.6.4 User to user group mapping table 2.6.5 Policy table 2.7 Language table 2.7.1 Language table 2.8 Open Issues 2.9 Comments

Visualization

The three following UML diagrams reflect the schema on this page.

• DB Visualization Topology
• DB Visualization Map
• DB Visualization Metrics

Update 16th august 2005: I have moved the visualization diagrams to be inline in this document. (Andreas@UNINETT)

Common Database Schema

A common database schema for the representation of network topology and metrics needs to be defined (layer 3). The initial draft has been issued by DFN Munich and Uninett using the experience gained in the CNM and Nemo project. The schema is going to be used as a common basis for the application of CNM and Nemo to JRA1. In addition, an XML export will be offered to allow for special data representations (e.g. for larger projects). A visualisation of the database schema can be found at Uninett (click on diagrams).

Even though the schema was only intended as a basis for the visualisation, the topology tables and other parts are also useful to denote the information that is needed for the framework. In addition, the schema could also be extended to for information about layer 1 and layer 2 needed by JRA3.

history - Generic Versioned Object Table

column name	datatype	description	further information
ID	long	unique identifer in table	primary key, not null
version	long	unique version	primary key, not null
name	String	human readable string for identification
valid_from	date_time	start of validity
valid_to	date_time	end of validity

Comments from old table:

• can be used to store a history of the elements
• we distinguish between automated and manual validity. For interfaces the automated validity can be derived e.g. from MIB ifAdminStatus, manual validity is used to filter elements by hand
• Remark: We assuming that the database assigns an OID for each entry in the database (true for PostgreSQL) which is different from the ID fields in previous tables
• "Add update_time and creation_time entries in most of the tables" (Thanassis)

Topology tables (OSI layer 3)

Visualization is updated 15th august 2005. If doing changes to table, contact Andreas@UNINETT to get the visualization updated too.

monitoring_objects - Monitoring object table (inherits from generic versioned object table)

column name	datatype	description	further information
identifier	string	Unique string identifying the object
name	string	Name of the object
descr	string	Description of the object

• Superclass for nodes and subnets (especially links)
• needed for distinction of measurements in table 3.3.
• Added identifier, name and description field. This is general for all monitoring objects. Identifier can be set using the observation point naming scheme proposed by UNINETT. (Andreas@UNINETT)

router - Node table (inherits from monitoring_object)

column name	datatype	description	further information
ip_mgmt_address	string	address to reach the node	Comment from Sven/Thanassis
type	integer	model type	linked to node model table
location	UTM	geographical position
domain	integer	link to domain table

• stores information about network nodes (routers, switches, monitoring stations (supposed to be relevant when using the visualisation for NOC/PERT purposes))
• type can be used to distinguish between different hardware components
• location can be used to derive locations in some of the maps
• domain is used to reflect NRENs/Geant transparency policy

router_type - Router type table

column name	datatype	description	further information
ID	int	for the enum model type
name	string	Name of the router type
descr	string	Description field of the router type

• Table to store information about different equipment models (Proposed by Thanassis)
• I renamed this to router_type, since we are only talking about routers anyway. (Andreas@UNINETT)

monitoring_station - Monitoring station table (inherits from monitoring_objects)

column name	datatype	description	further information
ip_mgmt_address	ip
ip_mgmt_address	location

• table to store information about monitoring stations (hardware component to perform measurements in the network, e.g. IPPM box)
• is tied to one interface (sufficient?)
• representation of measurements along a path?
• correspondence to GFD measurement points (multiple monitoring station could be seen as a single measurement point or vice versa)?
• description field removed, since inherited from monitoring object. (Andreas@UNINETT)

Monitoring station Monitoring object link table (inherits from generic versioned object table)

column name	datatype	description	further information
monitoring_station_id	long	refers to monitoring_station:ID
monitoring_object_id	long	refers to monitoring_object:ID

• DEPRECATED. When having one relation to monitoring objects, it should be enough. If you want to montor more than one monitoring objects, then create a group :) and a group is a monitoring object. If you agree Andreas@DFN, then remove this section. (Andreas@UNINETT)

metricdef_link - Monitoring station Metric definition link table (inherits from generic versioned object table)

column name	datatype	description	further information
monitoring_station_id	long	refers to monitoring_station:ID
metric_definition_id	long	refers to metric_definition:ID

interface - Interface table (inherits from monitoring_objects)

column name	datatype	description	further information
node_ID	long	unique identifier from node:ID	secondary key (unique with name), not null
speed	unsigned long	only to distinguish between 32/64 bit counters
type	unsigned long	media type MIB variable	selection of interesting interfaces
MTU_size	int
is_subinterface_of	long	proposal to deal with sub-interfaces

• stores information about single interfaces of a node
• information should be retrieved directly from routers (Comment from Sven)
• It should consist at least of a triple <interface IP address, system name (e.g., FastEthernet0/1), descriptive name (e.g., "Dante to DFN")>. Interface identifaction should not be maintained manually in the database, this would surely lead to inconsistencies. Interface identifications tend to change - cards are moved in routers, IP addresses are modified, etc. (Comment from Sven/Tom Kosnar)

• type can be used for filtering (e.g. filter null interfaces, loopback interfaces)
• sub-interfaces are not considered so far, because VLANs have not been identified to be interesting for the German Research Network. This could be different in GEANT.
• "I expect that the entire community will see a lot more sub-interface usage as people create sub-rate light paths across 10GE networks using vlans or other technology to carve circuits with defined QOS out of their infrastructure. Subinterfaces are also used extensively at exchange points. A very common model is to bring in a 10GE connection and run point-to-point vlans over the exchange to each of your low-volume peers. This is already happening at Starlight and ManLan. It will become even more popular in the US as PacificWave and AtlanticWave come online in the next year." (Comment from Joe Metzger)
• Interface names are derived from the if_decr MIB variable. These could be hard to understand for the users. Are there other solutions/proposals? (Comment by Joe Metzger)
• IP address could be used as interface names to be in correspondance with traceroute results (Comment from Joe Metzger)
• Removed name and description, since inherited from monitoring_objects. We cannot use if_descr and if_alias blindly in an interdomain context, since NRENs have different understanding and make different use of these properties. There must be a NREN specific translation of if_alias and if_descr into the identifier, name and description field. (Andreas@UNINETT)

interface_ip_address - Connection table (inherits from versioned object table)

column name	datatype	description	further information
ip_address	IP	IP Address v4/6
interface	long	refers to interface:ID
network	long	refers to subnet:ID

• Introduced as a result of the discussion about multipoint interfaces, because the database schema should be able to model them. Proposal for this and the following table by Olav.
• interface_ip_address is a relation between interface and network. (Andreas@UNINETT)

network - Subnet table (inherits from monitoring object table)

column name	datatype	description	further information
type	enum	distinguish between different types (link, VLAN, MPLS)
prefix	prefix	IP Prefix

• stores information about subnets (either links or multipoint connections)

Prior discussion about links/multipoint:

• Until now no multipoint links have been considered, because this makes the schema more complicated and these links are very seldom in German Research Network and Uninett. Are they more relevant to other networks? (Comment by Joe Metzger)
• "I do see the need for multi-point interfaces when zooming in. In our maps (Uninett) we have modeled multi-access network using the {ATM,MAC} switch/hub as a node to which each member node connects. VLANs are used in POPs and to customers. We may consider VLAN/ATM/(MPLS?) clouds as level 3 and the physical interfaces level 2." (Olav)
• "Currently the only multipoint transit interfaces within ESnet are at exchange points. I don't expect this to grow much. I believe it is worth it to think about how this will fit into the schema, but may not be important enough to actually change the design." (Joe Metzger)

prefix - IP prefix table (inherits from generic versioned object table)

column name	datatype	description	further information
network	long	identification from network:ID
prefix	String
name	String
descr	String
organization	String
use	String
domain	Domain

• allows for multiple IP prefixes for subnetworks
• to collected and updated automatically to avoid too much maintenance (e.g., each night)
• This table only have loose relations to network. (Andreas@UNINETT)

group - Monitoring object grouping table (inherits from monitoring object table)

column name	datatype	description	further information
grouptype	int

group_consist_of - Monitoring object grouping element table (inherits from generic versioned object table)

column name	datatype	description	further information
monitoring_object	long		refers to monitoring_object_grouping:ID
group	long		refers to monitoring_object:ID
order	int	optional ordering...

Map tables

Visualization is updated 15th august 2005. If doing changes to table, contact Andreas@UNINETT to get the visualization updated too.

map - Map table (inherits from generic versioned object table)

column name	datatype	description	further information
ID	long	unique identifer in table	primary key, not null
name	String	English map name (human readable)
URL	String	URL of background image

• stores information about visualisation maps
• can contain a background image e.g. if map uses geographical information

geographical_map - Geographical map table

• (inherits from map table, Uninett please add information from Nemo).

edge_coordinates - Map edge coordinate table (inherits from generic versioned object table)

column name	datatype	description	further information
map	long	refers to map:id
position	...	refers to position:id

map_element - Map element table (inherits from generic versioned object table)

column name	datatype	description	further information
ID	long	unique identifer in table	primary key, not null
name	String	English name (human readable)
min_resolution	omt	Referring to map resolution.
max_resolution	omt	Referring to map resolution.
monitoring_objects	long	unique identifier from monitoring_object:ID

• do we need multiple URLs for a single element?
• Generic mapping to topology tables, allows for more flexibility in the visualisation (represent interfaces of a router as nodes); is this flexibility needed?

map_node - Map node table (inherits from map element)

column name	datatype	description	further information
type	string	Type of node
position	value pair	position in map

• stores information about network node representation in a map
• position may be derived automatically if it is a geographical map

has_position - Map node Has position table (inherits from generic versioned object table)

column name	datatype	description	further information
map_ID	long	unique identifier (map:ID)
map_node_ID	long	unique identifier (map_node:ID)
position	...	describe position in map (x,y) or more complex?

action - Map element action table (inherits from generic versioned object table)

column name	datatype	description	further information
map_element	long	unique identifier (map_element:ID)
map	long	unique identifier (map:ID)
order	long	for ordering purposes in menus
title	string	generically specifies action to do
descr	string	generically describes action to do
href	string	URI

map_resolution - Map resolution

column name	datatype	description	further information
resolution	long

map_link - Map link table (inherits from map element)

column name	datatype	description	further information
map_node_from	long	unique identifier (map_node:ID)
map_node_to	long	unique identifier (map_node:ID)

• stores information about network link representations in maps
• link_layout describes how the link is displayed (e.g. arcs could be used for double connections), allow for clarity of

complicated maps

• (History table could also be used for maps. Will this result in size/redundancy problems?)

Connected map node-to-link table (inherits from generic versioned object table)

column name	datatype	description	further information
map_node_ID	long	unique identifier (map_node:ID)
map_link_ID	long	unique identifier (map_link:ID)

• allows for multipoint links
• This table is DEPRECATED. One link can only be attached to two nodes. To express multipoint links, we support inserting a network-cloud node, and attaching this to the respectively connected nodes. (Andreas@UNINETT)

Metric tables

This diagram was updated 16th August 2005. Contact Andreas@UNINETT to request any updates on the diagram.

The diagram below show an example of the templates expanded to three different data tables. One for ping and two for netflow. This diagram was updated 16th August 2005. Contact Andreas@UNINETT to request any updates on the diagram.

Metric definition table

column name	datatype	description	further information
ID	long	unique identifer in table	primary key, not null
name	String	English metric name (human readable)
data_source	String	way to access the data (with parameters)	direct SNMP, from general framework
interval	seconds	frequency of update
data_type	enum	data type for metric (e.g. percentage, unsigned long, status)
unit	string	unit used for measurement values

• Generic table for the description of displayed metrics
• Sven/Thanassis propose to skip interval field, because this can change over time. Problem: Where to store information about current interval instead?
• "I think that regular period is sufficient for measurement scheduling. There can also be next_run field with the timestamp of the next test execution. This field can be used a) for one time scheduling (not regular) and b) to help resolve cases when the current test execution was longer than the period. We can compute next_run as: next_run = next_run + ((1+int(current_time - next_run)/period))*period. Test instances should also belong to groups indicating which of them can overlap. Some test instances should not overlap, such as iperfs over non-disjunct network paths." (Sven)
• Do we need an accuracy field? Is it constant (-> should be part of metric definition table) or not (-> needs to stored for each measurement) (Comment from Thanassis)
• One-way latency should have accuracy, and it should be stored for each measurement. Clocks drift over time so the accuracy of One-way latency measurements can change over time. (Jeff)

(obsolete: Metric scheduling table (replaced by Metric methodology table) )

column name	datatype	description	further information
ID	long	unique identifer in table	primary key, not null
interval_type	enum(periodic,poisson)	Two different one-arg distributions
interval	int	seconds between two measurements (for poisson this is average interval)
duration	int	duration of a measurement

• "If there is going to be an extra table for the interval anyway, I would suggest adding fields to it to specify the "type" of interval. So far only periodic (regular) intervals have been discussed. We actually use a Poisson distribution for most of our "active" scheduling intervals as recommended by RFC 2330. It would be nice if that were supported as well. Poisson is simple because it is a single argument distribution just like a "periodic" distribution. (In the periodic case, the argument is just the interval, in the "Poisson" case the argument is mu, or the "average" interval.) It would not be difficult to make this extra table support "single" argument distributions, or even distributions up to some maximum number of arguments." (Jeff)

Tables for dealing with Metric methodology

Metric methodology template table

column name	datatype	description	further information
ID	long	unique identifer in table	primary key, not null
metric_methodology_template_time_resolution_ID	long	refers to metric_methodology_template_time_resolution:ID

Metric methodology parameter table

column name	datatype	description	further information
metric_methodology_template_ID	long	refers to metric_methodlogy_template:ID
parameterkey	string	methodlogy parameter keys describing the methodology (except scheduling information)
parametervalue	string	methodlogy parameter values describing the methodology (except scheduling information)

Metric methodology template time resolution table

column name	datatype	description	further information
ID	long	unique identifer in table	primary key, not null
interval_type	enum(periodic,poisson)	Two different one-arg distributions
interval	int	seconds between two measurements (for poisson this is average interval)
plusTimeTolerance	int	from NM-WG V1.0
minusTimeTolerance	int	from NM-WG V1.0
duration	int	duration of a measurement (NM-WG V1.0 testing time)

Tables dealing with measurement data

Measurement ID table

column name	datatype	description	further information
measurement_ID	long	unique identifier	not null, primary key
metric_ID	long	refers to metric_definition:ID	not null
metric_methodology_template_ID	long	refers to metric_methodology_template:ID	not null
scheduledTime	datetime	1	NM-WG V1.0 time parameter (for on-demand measurements); =0 if this is a regularly scheduled measurement with timeOffset taken from methodlogy_template

used as measurement request descriptor

Measurement data directory table

column name	datatype	description	further information
metric_ID	long	refers to metric_ID:ID	not null
table_name	string	actual name of data table
other fields ...

meta data about measurement data tables

Measurement subject table

column name	datatype	description	further information
ID	long	unique ID	not null

Measurement single subject table (inherits from measurement subject table)

column name	datatype	description	further information
monitoring_object_ID	long	refers to monitoring_object:ID	not null

... add other subclasses for more complex subjects like point..point or even paths (e.g. for traceroute)

Measurement data table (generic (meta data about contained in measurement data directory table), could contain different metrics if polled at same time interval)

column name	datatype	description	further information
measurement_ID	long	refers to measurement_ID:ID	not null
measurement_subject_ID	long	refers to measurement_subject:ID
time_stamp	date_time	actual time when measurement occurred
duration	int	seconds how long the measurement has actually been performed
value	any	type dependend from metric

• 3.3: conceptual tables; could be seperate tables for each metric if set of metrics for JRA1 has been confirmed; no differentiation between node and link measurement tables

Prior discussion:

• "I am not sure if we need separate tables for node and link measurements. Sometimes it is not clear if something is node or link measurement (such as an interface byte counter retrieved from a node). And we will also have values measured over the whole network path (between two monitoring stations). Perhaps each record can include one or two node identificators (for measurements between two node) with optional interface identificators (and these should be references to triples described in node table above)." (Comment from Sven)
• "I guess conventional snmp can be considered as interfaces and not the links linking them - i.e. a one node thing. The link connection between two or more interfaces does not need to have statistics then. We will need to model a passive monitoring device, that would tap into a fibre containing multiple lambdas." (Comment from Olav)
• "Ipfix speaks about Observation Points - I suggest that we also use it in a general way: for both passive Observation Points and interface Observation Points. "An Observation Point is a location in the network where IP packets can be observed. Examples include: a line to which a probe is attached, a shared medium, such as an Ethernet-based LAN, a single port of a router, or a set of interfaces (physical or logical) of a router." from Ipfix" (Comment from Olav)

Tables dealing with aggregated measurement data

Measurement aggregated data tables (generic (meta data about contained in measurement data directory table), could contain different metrics if polled at same time interval)

For Complex, pre-defined aggregationInterval Types:

Complex Aggregation Types Table

column name	datatype	description	further information
ID	long	unique identifier	primary key, not null
description	string		e.g. weekly, daily

Complex Aggregation Interval Table

column name	datatype	description	further information
ID	long	unique identifier	primary key, not null
aggregationID	long	refers to aggregation_type:ID	not null
startTime	datetime		not null
intervalLength	long
aggregationIndex	int		0..max, e.g. 0-7 for daily aggregation depending on the day of week
containingInterval	long		refers to aggregation_interval:ID

Complex Aggregation Data Table

column name	datatype	description	further information
aggregationIntervalID	long	refers to aggreagtion_interval:ID	not null
measurement_ID	long	refers to measurement_ID:ID	not null
monitoring_object_ID	long	refers to monitoring_object:ID
time_stamp	date_time	actual time when measurement occurred
duration	int	seconds how long the measurement has actually been performed
value	any	type dependend from metric

Metric table example

Metric definition table examples

column name	datatype	value	further information
ID	long	1	primary key, not null
name	String	link utilization	English metric name (human readable)
data_source	String	direct SNMP
interval	seconds	300	frequency of update
data_type	enum	percentage[2]	data type for metric (e.g. percentage, unsigned long, status)
unit	string	1	unit used for measurement values
ID	long	2	primary key, not null
name	String	round trip time	English metric name (human readable)
data_source	String	general framework
interval	seconds	300	frequency of update
data_type	enum	double	data type for metric (e.g. percentage, unsigned long, status)
unit	string	millisec	unit used for measurement values
ID	long	3	primary key, not null
name	String	available bandwidth	English metric name (human readable)
data_source	String	general framework
interval	seconds	300	frequency of update
data_type	enum	double[2]	data type for metric (e.g. percentage, unsigned long, status)
unit	string	Mbit/s	unit used for measurement values

Metric methodology template table examples

column name	datatype	value	description	further information
ID	long	21	unique identifer in table	primary key, not null
metric_methodology_parameterlist_ID	long	31	refers to metric_methodology_parameterlist:parameterlistID
metric_methodology_template_time_resolution_ID	long	41	refers to metric_methodology_template_time_resolution:ID

Metric methodology parameterlist table examples

column name	datatype	value	description	further information
parameterlistID	long	31	identifer in table
parameterkey	string	ip version
parametervalue	string	ipv4
parameterlistID	long	31	identifer in table
parameterkey	string	packetsize
parametervalue	string	1k kb
parameterlistID	long	31	identifer in table
parameterkey	string	class of service
parametervalue	string	premium ip
parameterlistID	long	32	identifer in table
parameterkey	string	methodlogy parameter keys describing the methodology (except scheduling information)
parametervalue	string	methodlogy parameter values describing the methodology (except scheduling information)
parameterlistID	long	32	identifer in table
parameterkey	string	methodlogy parameter keys describing the methodology (except scheduling information)
parametervalue	string	methodlogy parameter values describing the methodology (except scheduling information)

Metric methodology template time resolution table examples

column name	datatype	value	description	further information
ID	long	41	unique identifer in table	primary key, not null
interval_type	enum(periodic,poisson)	periodic	Two different one-arg distributions
interval	int	400	seconds between two measurements (for poisson this is average interval)
plusTimeTolerance	int	0	from NM-WG V1.0
minusTimeTolerance	int	0	from NM-WG V1.0
duration	int	10	duration of a measurement (NM-WG V1.0 testing time)
ID	long	42	unique identifer in table	primary key, not null
interval_type	enum(periodic,poisson)	poisson	Two different one-arg distributions
interval	int	100	seconds between two measurements (for poisson this is average interval)
plusTimeTolerance	int	0	from NM-WG V1.0
minusTimeTolerance	int	0	from NM-WG V1.0
duration	int	10	duration of a measurement (NM-WG V1.0 testing time)

Metric methodology time period table examples

column name	datatype	value	description	further information
metric_methodology_time_resolution_ID	long	41	refers to metric_methodology_time_resolution:ID
starttime	long	81111112344

Measurement ID table examples

column name	datatype	value	description	further information
measurement_ID	long	51	unique identifier	not null, primary key
metric_ID	long	1	refers to metric_definition:ID	not null
measurement_methodology_template_ID	long	21	refers to measurement_methodology_template:ID	not null
scheduledTime	datetime	0	NM-WG V1.0 time parameter (for on-demand measurements); =0 if this is a regularly scheduled measurement with timeOffset taken from methodlogy_template

Measurement data directory table examples

column name	datatype	value	description	further information
measurement_ID	long	51	refers to measurement_ID:ID	not null
table_name	string	msrm_data_51	actual name of table to save measurement data
other fields ...

Measurement data table examples

Table msrm_data_51:

column name	datatype	value	description	further information
measurement_ID	long	51	refers to measurement_ID:ID	not null
monitoring_object_ID	long	value	refers to monitoring_object:ID
time_stamp	date_time	811166336	actual time when measurement occurred
duration	int	30	seconds how long the measurement has actually been performed
value	double[2]	32, 43	type dependent on metric

Administrative tables

Domain table

column name	datatype	description	further information
ID	long	unique identifier in table
name	String
type	enum	differentation of domain types

• stores information about different domains.
• Does it make sense to have a differentiation between types of domains (e.g. NRENs, projects)

User table

column name	datatype	description	further information
ID	long	unique identifier in table
name	string
authentication	String	information needed for authentication

• stores information about single users
• Should this table contain more information like e-mail address, street address, organization, etc?

User group table

column name	datatype	description	further information
ID	long	unique identifier in table
name	String
domain	enum

• stores information about user groups

User to user group mapping table

column name	datatype	description	further information
user_ID	long	from user table:ID
user_group_ID	long	from user group table:ID

• table for mapping users to user groups. Necessary, because a user may belong to different user groups.

Policy table

column name	datatype	description	further information
ID	long	unique identifier in table
domain	enum
user group	enum
metric	enum
access rights	?

• Reflect different transparency policies (dependent on NREN, user/user group may also necessary); dependent on GFD
• Different users may be assigned different policies and these can differ for different sources of information (nodes, interfaces). (Comment from Sven)
• How should access rights look like?

Language table

Language table

column name	datatype	description	further information
referenced_OID	long	unique identifier in whole database	not null
language	int	number of language	not null
translated_name	String	translated name in specified language

• (Could be used for names in map/metric tables to allow for different languages)

Open Issues

• What are resonable durations for storing the data (depending on metric, user group interests)?
• Should we store only raw data and aggregate it on the fly or should we store aggregated data to increase the performance?
• Should the granularity of older data be degraded (e.g. aggregate 5 minute intervals into 1 hour intervals)?
• Which are the interests of different user groups (e.g. a network planner would like to see long term trends in the network usage, PERT people would like to have highly accurate current information to identify problems)

Comments

• Using RRD style aggregate sounds great initially but it frequently comes back and bites you. (Joe Metzger)
• Pre-computing aggregates is a useful performance optimization but it is no substitute for maintaining the detailed data. (Joe Metzger)
• "I think that we should store aggregated values as well. Each metricmay require different aggregation parameters. This requires more investigation." (Sven)
• "I think that we may want to store in the same database also information about scheduling of measurements." (Sven)
• Database should be populated automatically, e.g. via scripts, in order to avoid inconsistencies. Minimum information should be set by hand. (Thanassis)
• When refer to IP protocol, we should consider IPv4 and IPv6, in order to built a future-proof database. (Thanassis)