Model types of an entity [duplicate]

Question

Let's take a simple users table. I have a few different type of users. Let's simplify it into two, employees and customers, for the sake of the example, but keep in mind I do have much more.

So I have lots of common fields, like names, dates, Auth data etc... But I do have specific fields for each type of user. Again for the sake of the example let's just give employees a company_uuid and customers an id_card_number.

Now, I could add those two fields to the users table, and let them remain null when irrelevants, enforcing their correct use in the code. But as I mentioned I have multiple user types with lots of fields each. So I would like to avoid bloating an important table for nothing, filling it with null values everywhere.

I could create an employees table and a customers table, morphing my users table to one or the other with an ID and a type fields. This would work as well, but, nice way to add unwanted complexity for not much. Seems like a bad idea.

So I would like to know how you guys would do it. Thanks ahead!

Answer 1

Lots of columns, many being often NULL has issues. There are limits.

Putting all the attributes into a JSON string has problems (but handles NULLs nicely)

Using the EAV schema pattern has problems. (This is an extra table with multiple rows for the attributes. This forum is littered with people crying about the poor performance of such.)

Based on what little you have said, I recommend a hybrid. (It's not perfect, but avoids some of the major problems with speed, space, flexibility, etc.)

• Use columns for attributes that are rarely-null and/or frequently searched/filtered/sorted on.
• Use a single JSON column for all the miscellany. But don't expect to search/filter/sort on the values in it. Or, at least, understand that such will be clumsy and inefficient.

http://mysql.rjweb.org/doc.php/eav