CODEWITHSUNDEEP
databasedatabase-design
Kerwin Sneijders
Kerwin Sneijders

Reputation: 814

Best practise database prefixes in tables?

I have a database which has users and a todo list for these user (and a lot more tables but that's irrelevant).

A user can have multiple todo items but an item can only be linked to one user.

I was wondering how to create a table for the todo items?
- Should I just call it something like: todo-items
- As it is for users should I call it: users_todoitems
- Should I give it a prefix, like: u_todoitems?

I tried searching for best practices regarding these type of problems, but couldn't find a good answer.

Upvotes: 0

Views: 457

Answers (1)

Hari Harker
Hari Harker

Reputation: 791

Having different user tables is upto how normalized you want your db design to be. If you think deep, todos are actually a list which can have more than one task. So, it's more logical to connect todo list to user rather than connecting tasks. So, one user can have more than one todo list and each todo list can have more than one task.

I would advise to go with the following table design:

users table

  • user_id (primary key)
  • other_fields

user_todo_lists table

  • todo_list_id (primary key)
  • user_id_fk (foreign key)

todo_list_tasks table

  • task_id (primary key)
  • todo_list_id_fk (foreign key)
  • task_name
  • other_task_fields

This design works well even for your worst query of an user not having even one todo task. In such case your search is limited to user_todo_lists table and not the massive todo_list_tasks table.

Upvotes: 1

Related Questions