What is the best way of creating this database?

I am working on task manager website(a to-do list) but having difficult time on creating my database tables.

a school will be using this site so there will be thousands of people using this site. for this reason database must well organized and should easily retrieve the data.

in this website students will register and create an account.

they will be able to create/delete unlimited categories.

under these categories they can create/delete unlimited tasks.

users can also create/delete unlimited tags. Tasks will be assigned to these tags.

here is what i got, let me know what do u think?

user table
unique_id
name
email

Category Table
category_uniqe_id
unique_id (foreign key)(where I contact to user table)
category_title

task list table
task_unique_id
category_uniqe_id (foreign key)
task_title

tag table
tag_unique_id
task_uniqe_id (foreign key)
tag_title

if i use above schema, there might be millions of row under every table but user table, and that will cause large database…

what is the best way to set this up?

Thanks in advance.

This design looks good to me. Make sure the title fields in the task and category tables are paired with the unique id of the user to make a unique key. This will ensure one user doesn’t create the same tag or category twice.

I am a little concerned about the tag table though. Are you assigning tags to tasks for individual users? Or for the database as a whole? Right now it looks like you’re doing it for the database as a whole. If this is meant to be for individual users, then you should add a user foreign key there in the tag table. And also make sure to triple up the tag_title, task_unique_id, and user_id as a unique id so the same user can’t create the same tag for one tag TWICE.

Hope all this makes sense.

your task list table has a foreign key to the categories table, so this unfortunately means each task can be assigned to only one category

similarly, your tag table has a foreign key to the task list table, so this unfortunately means each tag can be assigned to only one task

you need a couple of many-to-many tables

Thanks for the reply Rudy and svcghostm

so if i remove foreign key to categories table from task list and add user_unique_id, that would assign each task to its creator, right?

task list table
task_unique_id
user_unique_id (foreign key)
task_title

for the tags, i still have no idea… how about this, I know still need to connect to tasks.

tag table
tag_unique_id
user_unique_id (foreign key)
tag_title

right

but how many categories can a task belong to?

that’s fine, but yeah, you still need a tag-to-task table