3 types of users with different data

Question

I am developing an application where I have multiple types of users. They all need the following data:

id | email | last_login

Beside these data, each have some type-specific data. For instance, I have a type user with additional data:

first_name | last_name

and a type shop with additional data:

shop_name | adress | phone | website

and finally a type supplier

supplier_name | adress | phone | category

I have been trying to find a solution, my first thought was to create 1 table and have an extra column extras with a JSON list of data - but this would be bad for SQL sorting purposes.

How can I do this in a proper way?

Answer 1

You can define an abstract super class client with 3 properties shared among all of them:

abstract class Client {
    int id;
    string email;
    date last_login;
}

Then you can define three separate children each representing one of you required type. So if you comply with object orientation concepts this will be solved without any problems. Are using an ORM or handling the data access layer yourself?

Answer 2

One simple approach is to make a table for each type that contains the necessary columns. This approach will duplicate the same columns in each table and include the distinct columns.

So you would create three tables:

USER (id | email | last_login)
SHOP (id | email | last_login | shop_name | adress | phone | website)
SUPPLIER (id | email | last_login | supplier_name | adress | phone | category)

Then in whatever language you are using, you could establish an object that just contains the generic information in the table. So basically create an object with just id, email, last_login. If your language of choice supports inheritance, this base object can be extended to include the fields for the specific derived classes.

This is known as Concrete Table Inheritance

For other methods of modeling inheritance in your database see this post.

Answer 3

You already divided the data in a proper way.

User
----
User ID
Email
Last Login Timestamp


Person
------
User ID
Last Name
First Name


Shop
----
Shop ID
User ID
Website


Supplier
--------
Supplier ID
User ID
Supplier Name
Category ID


Category
--------
Category ID
Category Name


Address
-------
Address ID
Address


Phone
-----
Phone ID
Phone


ShopAddress
-----------
Shop ID
Address ID


ShopPhone
---------
Shop ID
Phone ID


SupplierAddress
---------------
Supplier ID
Address ID


SupplierPhone
-------------
Supplier ID
Phone ID

A shop and supplier can have more than one address and more than one phone number.