CODEWITHSUNDEEP
mysqldatabase-design
Bhavin
Bhavin

Reputation: 2168

Database structure suggestion

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.

Upvotes: 0

Views: 303

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

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;

Upvotes: 1

glb
glb

Reputation: 36

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

Upvotes: 0

Officialk
Officialk

Reputation: 26

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

Upvotes: 0

Related Questions