Updated Feb 04 (5 days ago) by DossyNJ
Labels:	Phase-Design

Design

An overview of the system design.

Introduction

The system design will be guided by the requirements and assumptions that have been documented to date. Review both of those documents first to understand the context of the system design decisions reflected in this document.

Database Schema

The following are the key tables required to implement the application.

`facebook_cache`

`fb_type`	`VARCHAR(64)`	Facebook data type (uid, nid, etc.) cached
`fb_key`	`VARCHAR(64)`	Facebook data key
`value_str`	`VARCHAR(512)`	cached string value
`value_num`	`BIGINT`	cached numerical value
`stamp`	`DATETIME`	cache entry creation time stamp

facebook_cache SQL

This table will be used to cache data from Facebook, retrieved via the Facebook API. As per the Facebook Developer Terms of Service, these values can only be cached for up to 24 hours. A scheduled process will expire cache entries from the facebook_cache table periodically.

`lists`

`list_id`	`INT`	unique ID of list
`scheduled_dt`	`DATE`	day for which this entry is scheduled
`value`	`VARCHAR(512)`	list text (i.e., "You know you're in Jersey when ...")

lists SQL

This table will store the lists of the day. Assuming 5 years worth of lists, the table will contain 1,825 rows.

`submissions_(list_id)`

`submission_id`	`INT`	unique ID of submission
`list_id`	`INT`	list that this submission belongs to
`create_dt`	`DATETIME`	timestsamp of submission
`facebook_uin`	`BIGINT`	Facebook user's `uin` of submitter, storable indefinitely
`value`	`VARCHAR(512)`	submission text (i.e., "all gas stations are full-serve only")

submissions template SQL

This table schema is a template to be used for all submissions data tables. Submissions for a particular list_id will be stored in a corresponding table named submissions_(list_id), where (list_id) is the actual value itself.

`votes_(list_id)`

`vote_id`	`INT`	unique ID of vote cast
`list_id`	`INT`	list that this vote belongs to
`submission_id`	`INT`	submission that this vote belongs to
`create_dt`	`DATETIME`	timestsamp of vote
`facebook_uin`	`BIGINT`	Facebook user's `uin` of voter, storable indefinitely

votes template SQL

This table schema is a template to be used for all votes data tables. Votes for a particular list_id's submissions will be stored in a corresponding table named votes_(list_id), where (list_id) is the actual value itself.

► Sign in to add a comment