I have a database design question.
I am currently performing natural language processing on Twitter messages for intraday stocks data using three different NLP engines - Stanford NLP, IBM Watson, and OpinionFinder.
Both Stanford NLP and OpinionFinder use a polarity flag to denote sentiment - positive, neutral, and negative. I can identify this in the database a -1, 0, 1.
IBM Watson has five different percentages (from 0 - 100) on text known as anger, disgust, fear, joy, and sadness, and this can be stored as a float or integer (i.e. 0.9 or 90).
Each day (identified as date, in the format of YYYY-mm-dd) has three sentiment rows, one row for each NLP engine. So, there can be three identical symbol_id and date, which is why I think I should also add a nlp_engine to the composite unique key. My plan is to use symbol_id date nlp_engine as a composite unique key.
An alternative to this is, I also have a Prices table which stores the stock prices/futures data, and it has the following format:
id | date | symbol_id | ...
So, I could use the Symbols.id that references each day in Sentiments.prices_id, since I only gather intraday (daily) data.
Thus I want to create a table called Sentiments with the following columns:
id | symbol_id | date | nlp_engine | anger | disgust | fear | joy | sadness | polarity | created_at | updated_at
Explanation:
id - primary key
symbol_id (foreign key to a Symbols table which holds my stock symbols + a composite unique key to date and nlp_engine column)
date - (composite unique key with symbol_id and nlp_engine)
nlp_engine - (should I use a string for this or should I create a new table called NLPEngines and use a nlp_engine_id? This should also be a composite unique key with symbol_id and date)
anger - float
disgust - float
fear - float
joy - float
sadness - float
polarity - signed integer such as -1, 0, 1
I just want some critique on this database design - thank you.