Ares Database Tables

Sara Juel

Updated April 17, 2024 15:46

This page provides a complete alphabetical list of the database tables and fields used in Ares. Primary keys for the tables are designated in the PK column in each table.

Addons

PK	Field Name	Field Type
PK	ID	int
	Name	nvarchar(100)
	AddonType	nvarchar(50)
	Active	bit
	Package	varbinary(max)
	Settings	nvarchar(max)
	LastUpdated	datetime

CancellationReasons

PK	Field Name	Field Type
PK	ID	int
	Reason	nvarchar(255)
	DefaultNote	nvarchar(max)
	CancelType	nvarchar(50)

Courses

PK	Field Name	Field Type
PK	CourseID	int
	Name	nvarchar(200)
	CourseCode	nvarchar(20)
	Description	nvarchar(max)
	URL	nvarchar(255)
	Semester	nvarchar(50)
	StartDate	smalldatetime
	StopDate	smalldatetime
	Department	nvarchar(50)
	Instructor	nvarchar(50)
	CourseNumber	nvarchar(50)
	CoursePassword	nvarchar(50)
	MaxCopyright	money
	DefaultPickupSite	nvarchar(10)
	CourseEnrollment	int
	ExternalCourseId	nvarchar(255)
	RegistrarCourseId	nvarchar(255)
	CreationDate	datetime
	CreatedBy	nvarchar(50)
	CreatedVia	nvarchar(30)
	CourseInfo1	nvarchar(255)
	CourseInfo2	nvarchar(255)
	CourseInfo3	nvarchar(255)
	CourseInfo4	nvarchar(255)
	CourseInfo5	nvarchar(255)
	InstructorDisplayName	nvarchar(max)

CoursesSharedListsLink

PK	Field Name	Field Type
PK	CourseID	int
PK	ListID	int

CourseUserAuthorizations

PK	Field Name	Field Type
PK	CourseID	int
PK	LibraryID	nvarchar(50)

CourseUsers

PK	Field Name	Field Type
PK	CourseID	int
PK	Username	nvarchar(50)
	UserType	nvarchar(50)
	AddedBy	nvarchar(50)

CourseUserSubscription

PK	Field Name	Field Type
PK	CourseID	int
PK	Username	nvarchar(50)

CourseUserValidation

PK	Field Name	Field Type
PK	RegistrarCourseId	nvarchar(50)
PK	LibraryID	nvarchar(50)
	UserType	nvarchar(50)

CourseValidation

PK	Field Name	Field Type
PK	RegistrarCourseId	nvarchar(255)
	ExternalCourseId	nvarchar(255)
	Name	nvarchar(200)
	CourseCode	nvarchar(20)
	Description	nvarchar(max)
	URL	nvarchar(255)
	Semester	nvarchar(50)
	StartDate	smalldatetime
	StopDate	smalldatetime
	Department	nvarchar(50)
	Instructor	nvarchar(50)
	CourseNumber	nvarchar(50)
	CoursePassword	nvarchar(50)
	MaxCopyright	money
	DefaultPickupSite	nvarchar(10)
	CourseEnrollment	int

CrossListCourse

PK	Field Name	Field Type
PK	XCourseID	int
	ParentCourseID	int
	Department	nvarchar(50)
	CourseNumber	nvarchar(50)
	CourseCode	nvarchar(50)
	Name	nvarchar(200)

CustomDropDown

PK	Field Name	Field Type
PK	GroupName	nvarchar(100)
PK	LabelName	nvarchar(200)
	LabelValue	nvarchar(100)

Customization

PK	Field Name	Field Type
PK	CustKey	nvarchar(60)
	Value	nvarchar(1000)
	KeyDescription	nvarchar(max)
	Category	nvarchar(50)
	SubCategory	nvarchar(50)
	KeyType	nvarchar(50)
	ReadOnly	bit
PK	ProcessingLocation	nvarchar(10)

CustomizationLookup

PK	Field Name	Field Type
PK	ID	int
	CustKey	nvarchar(60)
	LookupValue	nvarchar(1000)

CustomizationTracking

PK	Field Name	Field Type
PK	CustKey	nvarchar(100)
	ValueChangedFrom	nvarchar(1000)
	ValueChangedTo	nvarchar(1000)
PK	ChangedTime	datetime
	ChangedBy	nvarchar(20)
PK	ProcessingLocation	nvarchar(10)

CustomQueues

PK	Field Name	Field Type
PK	QueueName	nvarchar(50)
	IsFlag	bit
	CanClone	bit

DailyTaskReports

PK	Field Name	Field Type
PK	ID	int
	Process	nvarchar(100)
	RunTime	datetime
	Result	nvarchar(10)
	Description	nvarchar(255)

DisplayStatuses

PK	Field Name	Field Type
PK	ID	int
	ItemStatus	nvarchar(255)
	WebDisplayStatus	nvarchar(255)

DocumentTypes

PK	Field Name	Field Type
PK	DocumentTypeName	nvarchar(50)
	DocumentTypeValue	nvarchar(50)
	Extension	nvarchar(50)
	Icon	nvarchar(200)
	ContentType	nvarchar(100)
	ForceReview	bit
	ForceReviewOnClone	bit

ElectronicFiles

PK	Field Name	Field Type
PK	ID	int
	FileName	nvarchar(max)
	FileSize	bigint
	Hash	binary(32)
	Pending	bit

EmailCopies

PK	Field Name	Field Type
PK	ID	int
	EmailDate	datetime
	EmailType	nvarchar(50)
	EmailReference	nvarchar(50)
	Staff	nvarchar(50)
	EmailTo	nvarchar(500)
	EmailFrom	nvarchar(500)
	EmailCC	nvarchar(500)
	EmailBCC	nvarchar(500)
	Subject	nvarchar(255)
	Body	nvarchar(max)
	Status	nvarchar(100)
	Note	nvarchar(255)

EmailRouting

PK	Field Name	Field Type
PK	ERoutingName	nvarchar(50)
	EmailTemplate	nvarchar(50)
	DefaultStatus	nvarchar(50)

EmailTemplates

PK	Field Name	Field Type
PK	Name	nvarchar(50)
	Description	nvarchar(255)
	Subject	nvarchar(255)
	CCAddress	nvarchar(500)
	Text	nvarchar(max)
	ToName	nvarchar(200)
	ToAddress	nvarchar(200)
	Type	nvarchar(50)
	SystemTemplate	bit
	FromAddress	nvarchar(200)
	FromName	nvarchar(200)

EventLog

PK	Field Name	Field Type
PK	LogDate	datetime
PK	Subject	nvarchar(20)
PK	Status	nvarchar(255)
	Username	nvarchar(50)

ItemCopyright

PK	Field Name	Field Type
PK	ItemID	int
	CopyrightProvider	nvarchar(50)
	Fee	money
	OrderHeader	nvarchar(50)
	OrderDetail	nvarchar(50)
	OrderedBy	nvarchar(50)
	OrderDate	datetime
	Terms	nvarchar(max)
	RightStatus	nvarchar(50)
	RightUpdated	datetime
	NumStudents	int
	NumPages	int
	Completed	bit
	PermissionDocFileName	nvarchar(50)

ItemCopyrightHistory

PK	Field Name	Field Type
PK	ID	int
	ItemID	int
	CopyrightProvider	nvarchar(50)
	Fee	money
	OrderHeader	nvarchar(50)
	OrderDetail	nvarchar(50)
	OrderedBy	nvarchar(50)
	OrderDate	datetime
	Terms	nvarchar(max)
	RightStatus	nvarchar(50)
	RightUpdated	datetime
	NumStudents	int
	NumPages	int
	Completed	bit
	PermissionDocFileName	nvarchar(50)
	EntryDate	datetime

ItemFlags

PK	Field Name	Field Type
PK	ItemID	int
PK	Flag	nvarchar(50)

ItemHistory

PK	Field Name	Field Type
PK	ItemID	int
PK	DateTime	datetime
PK	Entry	nvarchar(255)
PK	Username	nvarchar(50)

ItemNotes

PK	Field Name	Field Type
	ItemID	int
	AddedBy	nvarchar(50)
	NoteDate	datetime
	NoteType	nvarchar(50)
	Note	nvarchar(max)
PK	NoteID	int

Items

PK	Field Name	Field Type
PK	ItemID	int
	CourseID	int
	PickupLocation	nvarchar(10)
	ProcessingLocation	nvarchar(10)
	CurrentStatus	nvarchar(50)
	CurrentStatusDate	datetime
	ItemType	nvarchar(50)
	DigitalItem	bit
	Location	nvarchar(max)
	AresDocument	bit
	InstructorProvided	bit
	CopyrightRequired	bit
	CopyrightObtained	bit
	VisibleToStudents	bit
	ActiveDate	datetime
	InactiveDate	datetime
	Callnumber	nvarchar(100)
	ReasonForCancellation	nvarchar(255)
	Proxy	bit
	Title	nvarchar(255)
	Author	nvarchar(255)
	Publisher	nvarchar(50)
	PubPlace	nvarchar(30)
	PubDate	nvarchar(30)
	Edition	nvarchar(30)
	ISXN	nvarchar(20)
	ESPNumber	nvarchar(32)
	CitedIn	nvarchar(255)
	DOI	nvarchar(50)
	ArticleTitle	nvarchar(255)
	Volume	nvarchar(30)
	Issue	nvarchar(30)
	JournalYear	nvarchar(30)
	JournalMonth	nvarchar(30)
	Pages	nvarchar(100)
	ShelfLocation	nvarchar(100)
	DocumentType	nvarchar(50)
	ItemFormat	nvarchar(50)
	Description	nvarchar(255)
	CCCNumber	nvarchar(50)
	LoanPeriod	int
	Editor	nvarchar(255)
	ReferenceNumber	nvarchar(50)
	ItemBarcode	nvarchar(50)
	NeededBy	nvarchar(50)
	PagesEntireWork	smallint
	PageCount	smallint
	NatureOfWork	nvarchar(50)
	SortOrder	int
	ItemInfo1	nvarchar(255)
	ItemInfo2	nvarchar(255)
	ItemInfo3	nvarchar(255)
	ItemInfo4	nvarchar(255)
	ItemInfo5	nvarchar(255)
	ListItemID	int
	MediaExpirationDate	datetime

ItemSize

PK	Field Name	Field Type
PK	ItemID	int
	Limited	bit
	ItemSize	float

ItemTags

PK	Field Name	Field Type
PK	ItemID	int
PK	Tagger	nvarchar(50)
PK	ItemTag	nvarchar(255)
	TagAdded	smalldatetime
PK	TagType	nvarchar(20)

ItemText

PK	Field Name	Field Type
PK	ItemID	int
	ItemText	nvarchar(max)

ItemTracking

PK	Field Name	Field Type
PK	ItemID	int
PK	TrackingDateTime	datetime
PK	Status	nvarchar(50)
	Username	nvarchar(50)

LinkCheck

PK	Field Name	Field Type
PK	ItemID	int
	UpdateMethod	nvarchar(50)
	Status	nvarchar(50)
	StatusUpdater	nvarchar(50)
	StatusTime	smalldatetime
	HTTPCode	nvarchar(50)
	HTTPDescription	nvarchar(50)

LoanPeriods

PK	Field Name	Field Type
PK	LoanPeriodID	int
	Site	nvarchar(10)
	Description	nvarchar(50)
	Minutes	int
	Visible to Instructors	bit

LTIConsumers

PK	Field Name	Field Type
PK	ConsumerKey	nvarchar(50)
	SharedSecret	nvarchar(50)
	InstructorRoles	nvarchar(1000)
	AresWebURL	nvarchar(1000)
	CourseProxyRoles	nvarchar(1000)

LTIFieldMappings

PK	Field Name	Field Type
PK	AresFieldName	nvarchar(100)
PK	AresObjectName	nvarchar(100)
	LTIFieldName	nvarchar(100)

LTIKeys

PK	Field Name	Field Type
PK	ID	nvarchar(32)
	PublicKey	nvarchar(max)
	PrivateKey	nvarchar(max)
	Revoked	bit
	Created	datetime

LTINonceEntries

PK	Field Name	Field Type
PK	Nonce	nvarchar(50)
PK	RequestType	int
	TimeStamp	datetime

LTIPlatforms

PK	Field Name	Field Type
PK	ID	int
	AuthenticationURL	nvarchar(max)
	AresWebURL	nvarchar(1000)
	Issuer	nvarchar(max)
	ClientID	nvarchar(max)
	DeploymentID	nvarchar(255)
	Key	nvarchar(max)
	KeySetURL	nvarchar(max)
	InstructorRoles	nvarchar(1000)
	CourseProxyRoles	nvarchar(1000)

LTIPlatformMappings

PK	Field Name	Field Type
PK	LTIPlatformID	int
PK	AresFieldName	nvarchar(100)
PK	AresObjectName	nvarchar(100)
	LTIFieldName	nvarchar(255)

MessageBoard

PK	Field Name	Field Type
PK	MBID	int
PK	BoardId	int
	Thread	int
	Title	nvarchar(300)
	PostDate	datetime
	Username	nvarchar(50)
	Message	nvarchar(max)

OpenURLDestinations

PK	Field Name	Field Type
PK	Destination	nvarchar(50)
	DestinationURL	nvarchar(500)
	DestinationStatus	nvarchar(50)

OpenURLMapping

PK	Field Name	Field Type
PK	ID	int
	URL_Ver	nvarchar(50)
	rfr_id	nvarchar(100)
	AresAction	nvarchar(50)
	AresFieldName	nvarchar(100)
	OpenURLFieldValues	nvarchar(250)
	AresValue	nvarchar(100)

PrintDetails

PK	Field Name	Field Type
PK	SessionID	int
PK	ItemID	int

PrintSessions

PK	Field Name	Field Type
PK	SessionID	int
	Username	nvarchar(50)
	PrintDateTime	datetime
	PrintType	nvarchar(50)
	ItemsPrinted	int

ProxyUsers

PK	Field Name	Field Type
PK	InstructorUsername	nvarchar(50)
PK	ProxyUsername	nvarchar(50)

RightsholderJournals

PK	Field Name	Field Type
PK	RightsholderID	int
PK	Journal	nvarchar(255)
PK	ISSN	nvarchar(50)

Rightsholders

PK	Field Name	Field Type
PK	RightsholderID	int
	RightsholderName	nvarchar(200)
	Address1	nvarchar(200)
	Address2	nvarchar(200)
	Address3	nvarchar(200)
	City	nvarchar(100)
	State	nvarchar(100)
	PostalCode	nvarchar(50)
	Country	nvarchar(100)
	ContactName	nvarchar(100)
	Phone	nvarchar(50)
	Fax	nvarchar(50)
	Email	nvarchar(50)
	Comments	nvarchar(max)
	RightsholderType	nvarchar(50)
	PaymentPreference	nvarchar(50)
	RequestMethod	nvarchar(50)
	RightsholderURL	nvarchar(255)

Routing

PK	Field Name	Field Type
PK	RuleID	int
	Active	bit
	Status	nvarchar(50)
	Match	nvarchar(500)
	NewStatus	nvarchar(50)
	Description	nvarchar(255)

Semesters

PK	Field Name	Field Type
PK	SemesterName	nvarchar(50)
	StartDate	datetime
	EndDate	datetime
	ItemAdditionDate	datetime

ServerAddons

PK	Field	Field Type
PK	ID	int
	Name	nvarchar (100)
	Configuration	nvarchar(max)
	Code	nvarchar(max)

SharedListItems

PK	Field Name	Field Type
PK	ListItemID	int
	ListID	int
	PickupLocation	nvarchar(10)
	ProcessingLocation	nvarchar(10)
	ItemType	nvarchar(50)
	DigitalItem	bit
	Location	nvarchar(max)
	AresDocument	bit
	InstructorProvided	bit
	CopyrightRequired	bit
	CopyrightObtained	bit
	VisibleToStudents	bit
	Callnumber	nvarchar(100)
	ReasonForCancellation	nvarchar(255)
	Proxy	bit
	Title	nvarchar(255)
	Author	nvarchar(255)
	Publisher	nvarchar(50)
	PubPlace	nvarchar(30)
	PubDate	nvarchar(30)
	Edition	nvarchar(30)
	ISXN	nvarchar(20)
	ESPNumber	nvarchar(32)
	CitedIn	nvarchar(255)
	DOI	nvarchar(50)
	ArticleTitle	nvarchar(255)
	Volume	nvarchar(30)
	Issue	nvarchar(30)
	JournalYear	nvarchar(30)
	JournalMonth	nvarchar(30)
	Pages	nvarchar(30)
	ShelfLocation	nvarchar(100)
	DocumentType	nvarchar(50)
	ItemFormat	nvarchar(50)
	Description	nvarchar(255)
	CCCNumber	nvarchar(50)
	LoanPeriod	int
	Editor	nvarchar(255)
	ReferenceNumber	nvarchar(50)
	ItemBarcode	nvarchar(50)
	PagesEntireWork	smallint
	PageCount	smallint
	NatureOfWork	nvarchar(50)
	SortOrder	int
	ItemInfo1	nvarchar(255)
	ItemInfo2	nvarchar(255)
	ItemInfo3	nvarchar(255)
	ItemInfo4	nvarchar(255)
	ItemInfo5	nvarchar(255)
	MediaExpirationDate	datetime

SharedListItemText

PK	Field Name	Field Type
PK	ListItemID	int
	ItemText	nvarchar(max)

SharedLists

PK	Field Name	Field Type
PK	ListID	int
	ListType	nvarchar(50)
	ListReference	nvarchar(50)
	ListName	nvarchar(255)

Sites

PK	Field Name	Field Type
PK	SiteCode	nvarchar(10)
	SiteName	nvarchar(50)
	DefaultProcessingSite	nvarchar(10)
	AvailableForPickup	bit
	AvailableForProcessing	bit

Staff

PK	Field Name	Field Type
PK	Username	nvarchar(50)
	Password	nvarchar(50)
	FirstName	nvarchar(50)
	LastName	nvarchar(50)
	Description	nvarchar(100)
	ClientPermissions	int
	CustomizationManagerPermissions	int
	StaffManagerPermissions	int
	AddonPermissions	int
	StaffPasswordID	int

StaffPasswords

PK	Field Name	Field Type
	Username	nvarchar(50)
	ChangedDate	DateTime
	PasswordHash	nvarchar(255)
	ModifiedBy	nvarchar(50)

StaffLayouts

PK	Field Name	Field Type
PK	Username	nvarchar(50)
PK	LayoutType	nvarchar(150)
	Layout	varbinary(max)

SystemAlerts

PK	Field Name	Field Type
PK	AlertID	int
	AlertTitle	nvarchar(255)
	AddedBy	nvarchar(50)
	DateAdded	datetime
	ActiveDate	datetime
	InactiveDate	datetime
	IdentifierType	nvarchar(50)
	IdentifierValue	nvarchar(50)
	AllowDeletion	bit
	AlertMessage	nvarchar(max)

UserHotList

PK	Field Name	Field Type
PK	Username	nvarchar(50)
PK	ItemID	int

UserNotes

PK	Field Name	Field Type
PK	NoteID	int
	Username	nvarchar(50)
	NoteDate	datetime
	Note	nvarchar(max)

Users

PK	Field Name	Field Type
PK	Username	nvarchar(50)
	LastName	nvarchar(50)
	FirstName	nvarchar(50)
	LibraryID	nvarchar(50)
	Address1	nvarchar(50)
	Address2	nvarchar(50)
	Address3	nvarchar(50)
	City	nvarchar(50)
	State	nvarchar(50)
	Zip	nvarchar(10)
	Department	nvarchar(255)
	Status	nvarchar(50)
	EmailAddress	nvarchar(100)
	Phone1	nvarchar(20)
	Phone2	nvarchar(20)
	UserType	nvarchar(20)
	Password	nvarchar(50)
	PasswordHint	nvarchar(50)
	LastChangedDate	datetime
	LastLoginDate	datetime
	Cleared	nvarchar(5)
	ExpirationDate	datetime
	Trusted	bit
	AuthMethod	nvarchar(50)
	CourseEmailDefault	bit
	ExternalUserId	nvarchar(50)
	RSSID	nvarchar(50)
	UserInfo1	nvarchar(255)
	UserInfo2	nvarchar(255)
	UserInfo3	nvarchar(255)
	UserInfo4	nvarchar(255)
	UserInfo5	nvarchar(255)

UserSystemAlerts

PK	Field Name	Field Type
PK	AlertID	int
PK	Username	nvarchar(50)
PK	DateAlertRead	datetime

UserValidation

PK	Field Name	Field Type
PK	LibraryID	nvarchar(50)
	Username	nvarchar(50)
	LastName	nvarchar(50)
	FirstName	nvarchar(50)
	Address1	nvarchar(50)
	Address2	nvarchar(50)
	Address3	nvarchar(50)
	City	nvarchar(50)
	State	nvarchar(50)
	Zip	nvarchar(10)
	Department	nvarchar(255)
	Status	nvarchar(50)
	EmailAddress	nvarchar(100)
	Phone1	nvarchar(20)
	Phone2	nvarchar(20)
	UserType	nvarchar(20)
	ClearTextPassword	nvarchar(64)
	PasswordHint	nvarchar(50)
	Cleared	nvarchar(5)
	ExpirationDate	datetime
	Trusted	bit
	AuthMethod	nvarchar(50)
	CourseEmailDefault	bit
	ExternalUserId	nvarchar(50)
	UserInfo1	nvarchar(255)
	UserInfo2	nvarchar(255)
	UserInfo3	nvarchar(255)
	UserInfo4	nvarchar(255)
	UserInfo5	nvarchar(255)

WebFormValues

PK	Field Name	Field Type
PK	Formname	nvarchar(40)
PK	SubmitButtonValue	nvarchar(100)
PK	SubmitButtonText	nvarchar(100)

WebServiceAgents

PK	Field Name	Field Type
PK	Agent	nvarchar(450)

WebSession

PK	Field Name	Field Type
PK	Username	nvarchar(50)
	SessionID	nvarchar(40)
	Expiration	datetime
	RunMode	int

WebValidation

PK	Field Name	Field Type
PK	Formname	nvarchar(40)
PK	Fieldname	nvarchar(60)
PK	ValidationType	nvarchar(50)
	Validation	nvarchar(200)
	Error	nvarchar(200)
	Summary	nvarchar(200)
	ErrorTag	nvarchar(60)

ZProfiles

PK	Field Name	Field Type
PK	ProfileName	nvarchar(50)
	DBName	nvarchar(50)
	Port	nvarchar(10)
	SearchSystemNumber	nvarchar(200)
	SearchTitle	nvarchar(200)
	SearchAuthor	nvarchar(200)
	SearchISSN	nvarchar(200)
	SearchISBN	nvarchar(200)
	SearchDate	nvarchar(200)
	SearchKeywordTitle	nvarchar(200)
	SearchKeywordAuthor	nvarchar(200)
	ZType	nvarchar(50)
	ZIdentifier	nvarchar(50)
	ZCallNumber	nvarchar(50)
	ZAvailability	nvarchar(50)
	ZLocation	nvarchar(50)
	AvailabilityType	nvarchar(50)
	AvailabilityRegExp	nvarchar(255)
	AvailableTextType	nvarchar(50)
	AvailableText	nvarchar(50)
	NotAvailableTextType	nvarchar(50)
	NotAvailableText	nvarchar(50)
	SearchCallNumber	nvarchar(200)

ZSearches

PK	Field Name	Field Type
PK	SearchNumber	int
	SearchName	nvarchar(50)
	ServerConfigNumbers	nvarchar(100)
	DefaultSearch	nvarchar(20)
	SortOrder	int

ZServerConfig

PK	Field Name	Field Type
PK	ServerConfigNumber	int
	Name	nvarchar(50)
	Server	nvarchar(50)
	Port	int
	DBName	nvarchar(50)
	ServerType	nvarchar(50)
	SearchSystemNumber	nvarchar(200)
	SearchTitle	nvarchar(200)
	SearchAuthor	nvarchar(200)
	SearchISSN	nvarchar(200)
	SearchISBN	nvarchar(200)
	SearchDate	nvarchar(200)
	SearchKeywordTitle	nvarchar(200)
	SearchKeywordAuthor	nvarchar(200)
	AuthUser	nvarchar(100)
	AuthPass	nvarchar(100)
	ZType	nvarchar(50)
	ZIdentifier	nvarchar(50)
	ZCallNumber	nvarchar(50)
	ZAvailablity	nvarchar(50)
	ZLocation	nvarchar(50)
	AvailabilityType	nvarchar(50)
	AvailabilityRegExp	nvarchar(255)
	AvailableTestType	nvarchar(50)
	AvailableText	nvarchar(50)
	NotAvailableTextType	nvarchar(50)
	NotAvailableText	nvarchar(50)
	SearchCallNumber	nvarchar(200)