Recent Pages

Text Formatting Rules
Site SandBox
Change Management
Wiki Editor's How-To
Site Terms of Use
Site Copyright
Perl and NoSQL
Notes for Contributors
Table header
Philosophy of NoSQL

Links

NoSQL Home

Table of Contents

Wiki Editor's How-To

Text Formatting Rules

Wiki SandBox

www.strozzi.it on Twitter

Advertising

Please Support NoSQL !

Session

User ID
Password

Sign-in or register with OpenID

Campaigns

Database Schema

A Database Schema is a set of two files containing certain pieces of meta-information regarding the tables that comprise a NoSQL database. Such two files can take any names, but I will refer to them as the file schema and the field schema. Such schema files are just like any other NoSQL table, and can be manipulated with all the standard NoSQL commands and operators. In the explanations that follow I will make use of an hypotetical "school management" database. Our school has courses, and each course is followed by a certain number of students. We will therefore have three tables in the database:

• the course table, containing the list of available courses:

 
  Course  Credit  Room   Day         Time   Teacher 
  ------  ------  -----  ----------  -----  ------- 
  chem-1       2  HA-18  2003-10-12  14:00        1 
  cs-101       3  DB-1   2003-10-11  10:00        2 
  econ-1       1  RB-8   2003-10-10  09:00        2 
  his-10       3  HA-18  2003-11-12  15:00        3

• the student table, that lists the students in our school:

 
  Student  First    Last        Year    Phone 
  -------  -------  ----------  ----  ------- 
        1  Thomas   Jefferson      1     9876 
        2  George   Washington     1    87654 
        3  Ronald   Reagan         1  7654210 
        4  William  Clinton        1  6542109

• the course_student table, that tells us which students are enrolled into which courses:

 
  Course  Student 
  ------  ------- 
  chem-1        1 
  chem-1        2 
  chem-1        3 
  cs-101        2 
  cs-101        4 
  econ-1        1 
  econ-1        2 
  econ-1        3 
  his-10        1

And here's what a file schema table for our "school management" database will look like, as displayed by the NoSQL justify command:

 
  Table           Path              Edit 
  --------------  ----------------  ---------------------- 
  course          ./course 
  course_student  ./course.student  ./course.student-edits 
  student         ./student

Such a schema shows a typical many-to-many database relation (one student can take many courses and one course involves many students). Here's what the table columns mean:

Table

this is a symbolic name that is given to the associated table, and it is often the same name that is given to that particular database entity when the database is designed. The Table field must be unique across the whole database.

Path

this is the actual path -- either relative or absolute -- in the file-system, leading to the physical file that corresponds to the associated Table entry. In the sample schema above, relative paths are used, and to make clear that they are paths they were prepended by ./, which is optional for those files that are located in the current working directory, like in this particular example.

Edit

path -- either relative or absolute -- of the associated edit buffer, if any. See Big tables for more information.

The second Database Schema file is the field schema. It contains the list of all column names that comprise a database, associated with the table names they belong to. The table names are those listed in the file schema previously explained. Here is the field schema table of our sample school management database:

 
  Column   Table           Flags 
  -------  --------------  ----- 
  Course   course          K 
  Course   course_student  k 
  Credit   course 
  Day      course          h 
  First    student 
  Last     student 
  Phone    student 
  Room     course 
  Student  student         K 
  Student  course_student  k 
  Teacher  course 
  Time     course          h 
  Year     student

As you can see, fields that occur in multiple tables are listed multiple times, one time per table, and thus the fieldlist table key is the concatenation of Column and Table values.

The Database Schema files can contain additional, application-specific columns beside the basic ones explained above; all such columns should have names that begin with one upper-case letter followed by lower-case letters.

The Flags field in the fieldlist table provides further properties concerning each field, namely:

h

hidden field. Defining a field as hidden means that that field must be dropped from the result of NoSQL Brokering Language queries. The input tables may contain sensitive information in some of their fields, like passwords or other data that we do not want to expose. In the above example the fields Time and Day are flagged as hidden, meaning that we do not want them to appare on output.

k

partial key field. The full table key is made by the concatenation of multiple (usually two) such fields.

K

primary key field.

N

the field may not be null. This is implied for key fields, both primary and partial.

s

linear search on first (leftmost) column.

S

linear search by column name.

b

binary search on first (leftmost) column (the table is assumed to be sorted on such field).

i

indexed search on single-column index.

1-9

indexed search on multiple-column indexes; all fields having the same number are assumed to be part of the same multiple-column index file (e.g. table._x.Column1.Column2...). At most 9 multicolumn index files are supported for each table, but each index can be on as many columns as desired.

Each Flags field may contain multiple flags, such as kh, Kh, etc.