Data model of Reservation

Reservation

Presentation

Distributed

Database

Table of content

Principle
users
currentUserNo
resources-info
resources
bookings
applications

Principle

Reservation maintains its data in a RDBMS accessed using OLEDB.

Reservation has been tested with SQL server 7. It should also work with other databases that can be accessed using OLEDB.

You need to set up the connection string at configuration time. We provide examples for SQL server and Access. We also provide scripts for the database creation. For more information, look at resa-install.html.

Reservation uses transactions.

Reservation use six tables:

Reservation tables: users, resources, resources_info, currentUserNo, bookings and applications

users

The users table describes users.

Its key is the user identifier (id). key is a string of up to 10 chars.

Field	Meaning, usage and type
password	User password. Character string of up to 10 chars.
type	User type: 0 customer, 1 proxied customer, 2 employee. Short.
name	User name. Character string of up to 50 chars.
mail	eMail address. Typically not set for a proxied customer. Valid for other customers. Character string of up to 255 characters.
deltime	0 when the user is valid. Employee entries are not deleted but updated with their deletion time. Long. Contains the deletion time in FileTime format.
address	User address. Character string of up to 255 chars.
city	Character string of up to 50 chars.
state	State code. For the moment, only US and Canada state codes are supported. Character string of 3 chars.
zip	Zip code. Character string of up 10 chars.
country	Country code. Two chars.
region	Region. For instance Europe or North Am. Character string of up to 10 chars.
latitude	Latitude in N\|S:dd:mm.mmm format. Character string of 10 to 12 chars.
longitude	Longitude in E\|W:dd:mm.mmm format. Character string of 10 to 12 chars.

To display and select country and state names rather than codes, Reservation uses three files:

countries.csv	Country codes to names. Format country_code,country_name.
states.csv	USA state codes to names. Format state_code,state_name.
provinces.csv	Canada provinces (managed as states) codes to names. Format province_code,province_name

currentUserNo

currentUserNo has a single field, number. number is an integer.

This table is used for the creation of customer accounts using Retrieve.aspx.

A customer account has an id u currentUserNo and initially a password p currentUserNo.

Each time a customer account is created number is incremented.

resources-info

The resources_info table contains additional information about employees.

Its key is the user identifier (id). key is a string of up to 10 chars.

It contains an info field, which is a character string of up to 255 chars.

resources

resources describes Reservation resources.

Its key is made of:

The user (employee) identifier or a pseudo-user, a character string of up to 10 chars
The time, a long in FileTime format

resources contains a field (a short) for each slot from h800 to h1830.

The pseudo-users are:

total. Contains for each slot field the sum of the employee slot field values
booked. Contains for each slot field the sum of the bookings
available. Contains for each slot field the value of the total slot field – the value of the booked field

bookings

bookings describes the Reservation bookings. bookings contains four fields:

Field	Meaning, usage and type
id	User identifier of an employee (resource). Character string of up to 255 chars.
day	Booking date. Long. Contains the booking time in FileTime format.
hour	Slot hour. Short. From 8 to 18.
minute	Slot minutes. Short. Either 0 or 30.

applications

applications describes the Reservation instances defined in the same constellation.

Its key is url, the URL of the Location Web service on the peer Reservation instance. url is a character string of up to 255 chars.

Field	Meaning, usage and type
address	Address of the peer Reservation instance. Character string of up to 255 chars.
city	Character string of up to 50 chars.
state	State code. For the moment, only US and Canada state codes are supported. Character string of 3 chars.
zip	Zip code. Character string of up 10 chars.
country	Country code. Two chars.
region	Region. For instance Europe or North Am. Character string of up to 10 chars.
latitude	Latitude in N\|S:dd:mm.mmm format. Character string of 10 to 12 chars.
longitude	Longitude in E\|W:dd:mm.mmm format. Character string of 10 to 12 chars.
time	Record last modification time. Long. FileTime format.