Hello, What's the best database model to use for an user registration process that deppending on the user's type selected, some fields will be required or not.
Example: User Type A requires: Name, Email, Birth date and Gender User Type B requires: Company Name, Contact Person, Email, N. employees
I think the most commom is to create a table with all these fields and make them allow NULL.
What would you guys suggest?
I've previously done the same thing. As long as you store the customer type then it's not difficult to get the correct details.
You may consider 2 tables and use views to union them if there is going to be one table that is much larger than the other, although an index on the customer type will do much the same thing. By 'much the same ting' i mean make data of one type quick to access.
The nice thing about putting all the data in one table is you don't have to do any joins to get to the data, which helps with the speed of the query and ease of writing.
You could create a base "User" table that holds any common fields, and other tables that hang off User with a FK to the base User.ID. If there are no common fields, it might make sense to just have different tables, rather than one table with a bunch of nullable fields.
For consistency it is better to have one "main table" for users, in which the only field will be some unique user identifier that is common to users of both types (E.g., an ID, SSN or whatever).
It is then up to you how to represent the specialization.
One option is to have a second field in that table representing the type, and then two more tables with details, one for each type. When you query, you'll ensure that you are only looking in the TYPE_A table for details of a user if that user has a type of A.
A second option is to not have that field, and count on the fact that there is a record in a specific table to represent the type, but that is fairly risky.