Database Restructuring

[Sharabesh]

Current Articles Table Organization

Articles

• uniqueid (Primary Key)
• timestamp
• title
• authors
• abstract
• reference
• pmid
• doi
• neurosynthid
• experiements (Stored as JSON string)
• metadata (Stored as JSON String)

Proposed Articles Organization

Articles Table

• uniqueid (Primary Key, Hash Index)
• timestamp
• authors (could also be separated out, probably not worthwhile)
• title
• abstract
• reference (TODO: Potentially split apart as well)
• pmid (Unique Key)
• doi
• neurosynthid
• metadata (TODO: Potentially split apart as well)

Updates:

• Removed Experiments
• Included Hash index on uniqueid

Experiements Table

• Id: Primary Key
• Title
• Caption
• MarkBadTable
• Article ID (Foreign Key References Article(PMID OR Can Reference UniqueID))

Updates:

• Deleted Locations and moved to locations table
• Deleted Tags and moved to Separate Table as well
• Added Article ID Reference to Articles Table

Locations Table

• x coord
• y coord
• z coord
• Experiment ID (Foreign Key Field to Experiements Table)
• Primary Key (x,y,z,experimentID) --> Duplicate Elimination
• Structure: (Clustered Index?)

Updates:

• New table (useful for doing range searches (across Talairich coordinates) without issuing multiple calls)

Tags Table

• Name
• Ontology
• Agree
• Disagree
• Experiment ID (Foreign Key to Experiments (ID))
• Primary Key on (Name, Experiment ID)

Updates:

• New Table
• Makes it simpler to gather vote frequencies according to a specific experiment rather than experiment as a whole.