2

I have multiple tables in a Laravel app with 1-to-1 relationship such as users , users_settings , user_financial

And some 1-to-many relationships such as users_histories

My questions are:

1. Should I always include incremental id at the first?

for example is the id necessary in the Table #2 below?

Table 1:

id (primary,increments) , name, email, password

Table 2:

id (primary,increments), user_id, something_extra
 ^ why does every guide include this? // e.g. https://appdividend.com/2017/10/12/laravel-one-to-one-eloquent-relationships/

Can't I just use user_id as primary key and skip the incremental key? because I want to auto insert it on table 2 as soon as data is inserted in table 1.

2. How should I name 1-to-1 and 1-to-many tables in Laravel? `

I searched but didn't find any naming convention for different type of relationships...

Currently I do:

users table with primary key id is the base.

1-to-1: users_settings with foreign key user_id

1-to-many: users_histories foreign_key user_id

many-to-many: users_groups foreign_key user_id

should the first two tables be named settings/setting , histories/history instead? sorry I'm a little confused here.

Community
  • 1
  • 1
J. Doe
  • 812
  • 1
  • 15
  • 33
  • IMHO if you have an Eloquent Model you should have a primary key in the Model's table. If your table doesn't have a Model from the Laravel point of view you can do what you want but mostly what it works. Do you have a Model for user_settings? If yes you should have a primary if no, who cares. – dparoli Aug 23 '19 at 00:43
  • @dparoli Thanks, I searched a lot but didn't find how should I name `1-to-1` tables, should I just name the `users_settings` as `settings` or maybe `user_setting` or `users_setting`? sorry, I'm a little overwhelmed with all the different naming conventions on Laravel. – J. Doe Aug 23 '19 at 00:50
  • 1
    If the Model is `UserSetting` the table shoud be snake cased plural: `user_settings`, pivot tables should be snake cased singular in alphabetical model order: `role_user`. You can read here: https://webdevetc.com/blog/laravel-naming-conventions – dparoli Aug 23 '19 at 00:53
  • Note that I talked of `primary key` not of `incremental id` that are a subset. You can also check this question: https://stackoverflow.com/questions/41655535/laravel-5-naming-conventions – dparoli Aug 23 '19 at 00:57
  • @dparoli I'm reading the link, thank you very much – J. Doe Aug 23 '19 at 01:01
  • You're welcome, if you follow laravel conventions life is easier ;) – dparoli Aug 23 '19 at 01:02

2 Answers2

3

I actually asked a similar question around 2 days ago. Its up to you but I'd say yes. In my case if I don't auto_increment all my ids in the related tables, data won't be associated with the correct user. However, there is an argument for saying auto_increment columns should not be used in this case, but they are useful for other things. According to some, the relationships might not be as meaningful so it'd be up to you and down to the specifics of you data tables for how meaningful the relationship will be. Regardless, you should research more into the advantages of auto_incrementing all your ids in related tables, as well as possible disadvantages before deciding what you want to do. Either way is fine, but they offer different advantages and disadvantages- which you'll need to compare and what works best for your specific case.

DouglasstheDog
  • 49
  • 8
  • Thank you, I decided to not use `id` and go with `user_id` as both primary_key and foreign_key in `settings` table, just have to figure out if it's okay for the same column to be both in Laravel – J. Doe Aug 23 '19 at 01:11
1

  1. This is a well debated topic about the primary key. IMHO, No, you shouldn't. Every column in database should have a purpose. Following this, for your example, I agree that the auto_increment id is redundant and this is simply because it doesn't have a purpose. The second table is still uniquely describing the user so that the primary key should be the user_id.

    Beside the above, there is another principle for me to decide whether I need the auto_increment id: whether I can see a table as an entity. For example, user is clearly an entity, but a relationship is not (in most cases), i.e., composite key can serves the purpose. But when an relationship table is extended to have more attributes and it starts to make sense for it to have an auto_increment id.

  2. I don't have much experience on Laravel, but the naming for a database table should not be dictated by a framework. Comparing history and user_history, what a new DBA or developer expect from the two names without looking its data? user_history describes the table more precisely

Community
  • 1
  • 1
Jacob
  • 1,776
  • 14
  • 11