I am trying to create a database table for application form which will contain 70 - 80 fields i.e 70 - 80 table columns.
This table is for multiple clients but out of 80 fields nearly 30 fields will be same for every user and remaining will be changes to every user.
Note: Every client will have multiple users under them who fills that application form.
I though of creating tables in 2 ways.
1st one:
1st table contains same fields [sno, firstname, lastname, ..] -> for all users 2nd table contains 4 columns [sno, field_name, value, users_id]
2nd one:
1st table will have all columns irrespective of the users (that means I will add all the client columns in a single table) in 2nd table i will maintain user_id and respective columns associated with it.
Which is the best way and if there is any other best way please let me know.
Common fields for all clients: Personal details, Family details, educational details etc
There are some other details that will be different to every client
The best way to handle this sort of design is to use EAV concept.
EAV : Entity Attribute Value
you need to make few tables :
users (id, create_date, status)
attributes(id, title, status) // it will contains all attributes like firstname, lastname, etc etc)
attribute_to_users (id, attribute_id, user_id, attribute_value) // it will contain attribute value with respect to attribute and user
For example :
attribute_to_users (1, 1, 1, Naveed)
attribute_to_users (1, 2, 1, Ramzan)
It means for same user, attributeId : 1 (firstname) value is Naveed and for 2nd attributeId which is Last name value is Ramzan
Same Drupal and magento doing.