Users & UserDetails Tables

Hi guys,

I am having a debate with myself whether it’s better to include all user account details in a single table row or if the data should be stored in a separate table?

I created 2 tables, users table stores the core user data (id, password hash, time stamps) in its own table and a secondary table (user_details) stores full name, phone number, address, etc etc. My initial thought here was that if more columns need to be added in future it can be easily added to the secondary table without having to mess around with the primary user auth table.

I then setup a hasOne relationship between the User and UserDetails model.

I’m now running through my user tests and beginning to wonder why this is beneficial since instead of calling $user->full_name I’m having to call $user->details->full_name

Any advice would be appreciated 👍

Snapey

7 years ago

Best Answer

Level 122

Yep, no benefit at all in having two tables.

The only time this would be a benefit is where there are a lot of rows in one table and only a few of the other.

Take for instance, most users only have logon credentials, but some have given you all their address details and other personal information.

Rather than the majority of users having loads of empty fields you would create a seperate table to hold the stuff only a few users have.

To be of any meaningful value though, you would need to be talking about 10's of thousands of users and a small percentage of those with the extended records. The info has to be clearly defined though as soon as just one value is written into the extended record then you have that whole row and all the empty fields that you were trying to avoid.

jlrdw

7 years ago

Level 75

As far as other information, if it's just 2 or 3 more fields, fine. But if it's 20 fields on users, (could be employees) and if most of those fields were required, then yes separate tables for the other information.

Many times some common sense and planning it out with pencil and paper is very helpful.

And for most apps I just have a field added to users table:

   roles 
-------------
admin
bkeep     // for bookkeeper
admin,bkeep   // both roles
user

A simple comma separated list of roles.

Can easily check them via in_array something like:

    public static function chkRole($role = null)
    {
        $userrole = Auth::user()->role;
        $checkrole = explode(',', $userrole);
        if (in_array($role, $checkrole)) {
            return true;
        }
        return false;
    }

Note

Not saying do anything like I do, just an example.

Above I am just verifing the required role of a method is alowed by one of the roles of the logged in user.

But of course do your own logic.

Bottom line let the users table be for auth primarily. Have something like a personnel table if needed for other data.

1 like

Please or to participate in this conversation.