How to create a database schema?

Question

I am creating a database for the portal. In particular, I have a user table that contains the columns: id, username and password.

There are also three types of users: buyers, sellers and brokers, and each user has a separate table with columns: name, description, mobile, introducer, etc. There is no index column in the broker's table.

Based on this project, I want to create a two-stage registration form with information about the first login step and profile information for the second step.

Now business rules determine that the user can be a buyer, seller or broker. A user can have no more than one profile (buyer, seller or broker). I want to save login information and profile information separately in the database

What I've done:

I created a separate table for users, brokers, buyers and sellers with a user ID as a foreign key in the table of buyers, brokers and sellers.

Now my question

• How to create tables for this design?
• How to specify foreign key restrictions?

I am new to database design and all help is appreciated. Thanks in advance.

Answer 1

As a “User can have no more than one profile”, it seems to be useful for you to add a user_profile table, which will then be “subtyped” by broker , seller and buyer , adding additional fields, if applicable.

A user will have 1: 1 with user_profile and user_profile will have 1: 0/1 with broker , seller and buyer . I would consider using user_id as the primary key to all of these.

I think you will also find this answer helpful.