I am creating a database that takes in daily inputs such as:
- Date
- Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...)
- Rating of the date
There's more but these are probably the only relevant inputs regarding the problem. The problem is how do I construct my tables so that I have stored somehow the specific duration an event lasts. Currently what I have is:
- A users table storing user log in credentials (1)
- A rating table to store input date, rating of the date, etc... (2)
- A tags table storing unique tags (3)
I will use the tags and ratings to look for possible patterns in the future in case that's relevant.
I don't find it difficult at all if I just had to deal with tags alone related to specific dates, as I would just have to have an unique table of tags and associate the tag_id with a date. But I have no clue how I store the duration of a tag on a specific day efficiently. I could of course only use a single table, accept redundancy with overlapping tags on different days and store tags, events and duration in a single string field. But that doesn't seem efficient to me. I am very novice when it comes to databases and I appreciate the help.
I am using Python 3.7.2, SQLite and Flask-SQLAlchemy 2.4.0 to construct the tables.
Like so:
(1)
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
password_hash = db.Column(db.String(128))
(2)
class Rating(db.Model):
__tablename__ = 'rating'
id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.Date, unique=False, nullable=False)
rating_day = db.Column(db.Integer, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
# etc...
(3)
class Tags(db.Model):
__tablename__ = 'tags'
id = db.Column(db.Integer, primary_key=True)
tag = db.Column(db.Integer, unique=True, nullable=False)