0

I'm stuck in database design . I have to show HTML form fields with specify that which fields are mandatory or not and visible or not from database for so many forms.

I have to put that option for configuration in admin panel. So, Admin select forms and fields and checked that it's mandatory or not / visible or not and that configuration is set for that specific user.

Note :- Only if the user changed the configuration otherwise they must come from default configuration.

Database Design for Form Table,

form_id - int(11) form_name - Varchar(255) screen_ame - Varchar(255)

Field Table,

field_id - int(11) field_name - Varchar(255) form_id - int(11)

form_field Relation Table,

field_id - int(11) user_id - int(11) mandatory - enum('1', '0') visible - enum('1', '0')

So, from these three tables I can get all data that gives forms data and which also includes that which field is mandatory and visible.

But , I'm stuck in this logic that forms fields are only changed(setting overwrite) when user changed the configuration otherwise they must come from default configuration.

Improve this question

3 Answers 3

Reset to default
1

I'd simply add the two flags to the fields table. So for every field you have the default values directly there and users can override these with own entries in the user_fields table.

Fields Table

 field_id - int field_name - varchar(255) form_id - int is_mandatory - bool is_visible - bool

User_Fields Table

 field_id - int user_id - int is_mandatory - bool is_visible - bool

A query:

select f.field_id, f.field_name, coalesce(uf.is_mandatory, f.is_mandatory) as is_mandatory, coalesce(uf.is_visible, f.is_visible) as is_visible from fields f left join user_fields uf on uf.field_id = f.field_id and uf.user_id = 321 where f.form_id = 123;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Ohk great. Thank you for this query. I got my solution from your answer. Can we store all form element as a whole json string and after fetching from database we can do some work out of that json array on client side is it good practice to storing data all data in single json array/string?
JSON is a NoSQL approach. Great for, say, optional data. If you store very different items in a product database where a shirt has or has not a collar or a pocket while a washing machine has a capacity and a spin speed, and you don't want to create a complex database structure to model this, then you can store this as XML or JSON. But with your simple forms and fields, what would you gain? There is nothing complicated about the structure and the data is easy to query in a pure relational model. So, yes it's possible to use JSON, but I would not recommend it, for I see no sense in it here.
Ohk got you man. Thank you for your suggestion. I'll go with your suggested database structure. Again thank you for your help.
0

if you want the default value to be not mandatory or visible you can set the default value to that in whichever db you are using so set it to default in the back ground if the user(admin) has not specified anything enter image description here

Improve this answer

1 Comment

Yeah got you man but in your solution we can't figure out the logs for that row because I need both default (core) configuration as well changed.
0

Consider user_id = 0 or -1 for default setting and user_id > 0 for the actual users.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.