best and optimal way of storing data in DB

Question

I have a situation where I have to store some flags and data in DB. The contents are like

is_read ---> to check whether user has read case study , flag 0 or 1

is_submitted ---> to check whether user has submitted the case study flag 0 or 1

url_file ---> url of the file uploaded by user , data type varchar(100)

Each case study can be accessed by many users.

So which among the two is optimal and best way of doing it?

1) storing every thing in one table , no duplication here
id | user-id | case_id | is_submitted | is_read | file_url

2) breaking down to table for each constraint as they are not related to each other

id | user-id | case_id
<fk>

id | is_submitted
id | is_read
id | file_url

i need all data so in 2 case it will have more joins.

And also the columns are not related to each other.

Answer 1

This is too long for a comment.

To me, it would seem that a case study is submitted by a single user. If so, the CastStudies table should have a column called SubmittedBy.

Similarly, I would imagine that a case study has a single URL. If so, it should have a column called FileURL. If there are multiple URLs, then you would want another table, CasestudyFiles to store them.

As for the read flag, this seems like a many-to-many relationship between case studies and users. As such, you would want a junction table, with columns like:

• CasestudyId
• Userid
• ReadDate

However, I am imagining all these things, because your question doesn't really explain the entities and the relationships among them.

EDIT:

Based on your comment, I would envision a table like this:

create table UserCasestudies (
    UserCasestudiesId int not null auto_increment primary key,
    CastStudiesId int not null references Casestudies(CaseStudiesId),
    UserId int not null references Users(UserId),
    ReadDatetime datetime,
    SubmittedDatetime datetime,
    UploadDatetime datetime,
    URL varchar(100)
) ;

You can add flags if you like, but I think looking at date/time of the actions is potentially more useful.