Database schema

Updated on 01/28/2013

Database notes

Work-in-constant progress. For a more up to date schema definition check the Doctrine Config Files

A (slightly out of date) database diagram is available here

Items table

Name	Required?	Type	Description
id	No. Auto-generated by the database engine	integer	Item id
user_id	Yes	integer	Id of the Zeega user that added the item to the database
title	No	string	The title of the item
description	No	string. Max size 500 characters.	The description of the item
text	No	string	Text
uri	Yes	string. Max size 500 characters.	Uri of the item. Used for display and playback. E.g. Flickr photo uri
attribution_uri	Yes	string. Max size 500 characters.	Uri of the item. Used as reference to the original media source. E.g. Flickr photo attribution uri
date_created	Yes	datetime	Date when the Item was added to the database
date_updated	Yes	datetime	Date when the Item was last updated on the database
archive	Yes	string. Max size 50 characters.	Archive to where Item belongs. E.g. Flickr, Reischauer Institute, Youtube, Collection
media_type	Yes	string. Max size 20 characters.	Media type of the item. Currently the following types exist Video, Audio, Text, Image, Tweet, Document, Website
layer_type	Yes	string. Max size 20 characters.	Layer type of the item.
thumbnail_url	No	string. Max size 500 characters.	Uri to the item's thumbnail
child_items_count	Yes	integer	Number of child items. If the item is a collection this field will have the number of items in it.
media_geo_latitude	No	float	Item's latitude. Currently using the EPSG:4326 projection.
media_geo_longitude	No	float	Item's longitude. Currently using the EPSG:4326 projection.
location	No	string. Max size 100 characters.	Item's location name (e.g. city name, street name)
media_date_created	No	datetime	Date when item was created on the source. E.g. date extracted from the Flickr API response when adding a photo.
media_date_created_end	No	datetime	Complimentary to the media_date_created field for items that don't have a creation date but have a time interval.
media_creator_username	Yes	string. Max size 80 characters.	Username at the media source of the user that created the item. E.g. twitter handle
media_creator_realname	No	string. Max size 80 characters.	Real name at the media source of the user that created the item. E.g. twitter display name
license	No	string. Max size 50 characters.	Real name at the media source of the user that created the item. E.g. twitter display name
attributes	No	PHP array	Array of weakly typed attributes
tags	No	PHP array	Array of tags
id_at_source	No	string	Id of an item at the source. Usefull to do incremental imports when importing large datasets from external databases.
enabled	Yes	bool	Used to delete items; if 1 the item is active, if 0 the item is considered deleted.
indexed	No	bool	Used for indexing with SOLR. Deprecated and should be ignored.
geo_latlng	No	PostGIS POINT	Used in Postgres and PostGIS only. Used to geolocate items in Geoserver.

Media and layer types

Item	Media type	Layer type
YouTube video	Video	YouTube
Website	Website	Website
Document (DocumentCloud)	Document	DocumentCloud
Document (PDF)	Document	PDF
Testimonial	Text	Testimonial
Image and Photos	Image	Image
Audio file	Audio	Audio
Tweet	Tweet	Tweet