CODEWITHSUNDEEP
phpmysqllaravel
J. Doe
J. Doe

Reputation: 1009

Should I include auto-incremental id in all related tables?

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.

Upvotes: 2

Views: 692

Answers (2)

Jacob
Jacob

Reputation: 1936

  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

Upvotes: 1

DouglasstheDog
DouglasstheDog

Reputation: 49

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.

Upvotes: 3

Related Questions