Database design question: Tags with values(duration) linked to them

Question

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)

Answer 1

Microsoft SQL Server guy here. Not sure if I'm understanding how you are capturing your events, but an approach I've used is to capture a start time and an end time, then calculate the duration after the fact. If a person is manually starting and ending an event, then that would be my approach. As far as associating tags to events, a join table would address the issue of multiple tags on a single event.

/****** Object:  Table [dbo].[event_tags]  
CREATE TABLE [dbo].[event_tags](
    [id] [int] NOT NULL,
    [event_id] [int] NOT NULL, --Foreign key to table events' primary key
    [tag_id] [int] NOT NULL --Foreign key to table tags' primary key
)

Answer 2

The primary question seems to be

how do I construct my tables so that I have stored somehow the specific duration an event lasts

and

how I store the duration of a tag on a specific day efficiently

John Herbet already suggests storing two time values and then finding the difference. Since it appears that you are relying on Python and SQLAlchemy libraries, then perhaps the best approach is to store two datetime values. (I'm not an expert on Python or SQLAlchemy, but it looks like there is both a date and datetime database type.) Then utilize standard Python libraries to find differences and otherwise manipulate the starting and ending times that are stored with an event (or tag?).

For a more sqlite-centric method, based on SQL queries and sqlite built-in functions, I suggest this webpage, since it has some great examples of using the juliandate() function to manipulate date/time values. Julian dates are stored as a decimal number that represents days elapsed since some very long-ago epoc. The conveniences is that numerical differences and additions of the numbers can reveal time spans. This is worth looking into if you end up working with sqlite SQL directly.

---

The relationship between what you call "events" and "rating" and "tags" is not clear. The primary purpose seems to be about recording "events", yet there is no definition of an "event" table! And further, the question mentions possibility of "overlapping tags", but very generically a tag usually refers to basic label or category. I would either rework that table schema to more naturally reflect what you're trying to store, or update the question with a better explanation if that detail is even relevant.