Showing revision 27

DBdesign

This notes were born during the discussion in SAI on Jan 2, 2006.

Notes to CAS server DB design

CAS stands for Catalogs Access Server (after http://casjobs.sdss.org/CasJobs/). It doesn't relates to VO specification, but could be used to build VO access to catalogs.

Requirements to CAS metadata:

  • ability to access any catalog.table.column
  • ability to design widgets - short name, detailed information, contacts,etc.

We need following tables and columns inside them (CAS objects mapped to relations):

ucd

  • int ucd_id
  • varchar ucd_name
  • ucd_properties

index_type

  • int index_id
  • index_properties

catalog_list

  • int catalog_id
  • varchar catalog_name
  • catalog_properties

table_list

  • int table_id
  • int catalog_id
  • table_properties
  • int ra2000_attribute
  • int dec2000_attribute

attribute_list

  • int attribute_id
  • int table_id
  • attribute_properties

index_list

  • int index_list_id
  • int index_id
  • int attribute_id

Notes:

  • Bolded are table names
  • List items are columns
  • "*properties" columns designates all other unspecified columns, mostly used for designing widgets, i.e., contact information, short label, long description, version, date of last changes, etc.
  • All catalogs should live in different database schemes !
  • CAS tables should live in separate cas metadata scheme (with special access roles !)

Discussion

Let us discuss all DB design issues here or at least post here most important decisions.

  • We decided to check if it is possible to make use of graphviz for DB design vizualization. IZ will try to check it out.
  • Oleg: Use plural forms for table names and *_*_map for maps
  • Oleg: Use SQL (\dt+ displays comments).