HTML form generation from the database and store value into the database

Question

I have a "t_form" table and a "t_attribute" table. It looks a little bit like below.

Form table

form_id | form_name    | description
-----------------------------------
1       | Laptop       | Possible attributes are Model, Screen Size, OS, Cd Player
2       | Mobile       | Possible attributes are Model, OS
3       | Tablet       | Possible attributes are Model, Screen size, OS

Attribute table

attribute_id | attribute
------------------------
1            | Model
2            | Screen Size
3            | OS
4            | Cd Player

1. Possible attributes of type Laptop form are Model, Screen Size, OS, Cd Player.
2. Possible attributes of type Mobile form are Model, OS.
3. Possible attributes of type Tablet form are Model, Screen size, OS.

Form_Attribute table

form_id | attribute_id
----------------------
1       | 1 
1       | 2
1       | 3
1       | 4
2       | 2
2       | 2
3       | 2
3       | 2
3       | 2

I will generate the HTML form like below(with form fields) according to the selection of form type(either Laptop, Mobile or Tablet) by the user from the database.

For Laptop:

For Mobile:

For Tablet:

Questions: Here are my questions.

1. What is better approach to store the data entered by the user into the database.
2. What is better approach to retrieve the data from the database, because I want to achieve the filter option, for example: I want to fetch the result which laptop screen size is avilable from 14 to 16.

Filter:

Please provide a feasible solution with some example for better understanding to me and help me to achieve this.

Answer 1

I think you can go for a table like

Form_Attribute_Values or Items

item_id |    form_id | attribute_id | value
------------------------------------------------
1       |    1       | 1            |  SuperLap
1       |    1       | 2            |  15
1       |    1       | 3            |  MS-DOS 6
1       |    1       | 4            |  Toshiba

In this way you will be able to query the single item entered, but also to filter by Form, Attribute or Attribute Value.

You Form_Attribute table will be very handy generating the form for a specific Form (i.e. when a user wants to enter a Laptop or a Mobile phone).

The Items table will be the place where you will store the items after an insert or where you will look to query them.

You can also add at the end a timestamp column to store when the item was saved and a User_id column if your data will be populated by multiple users. And maybe an additional column for the availability too if you can store items not available in store or you can keep availability in a separate table.

A query for a laptop with screensize 14-16 will be like this if you can get the form and attribute id from your filtering form:

    SELECT item_id, 
           form_name, 
           attribute_name,
           value
INNER JOIN form 
        ON item.form_id = form.form_id 
INNER JOIN attribute 
        ON item.attribute_id = attribute.attribute_id 
     WHERE item_id IN (-- here I select for Laptop and Screen Size dimension
                       SELECT item_id 
                         FROM item
                        WHERE form_id = 1
                          AND attribute_id = 2
                          AND item.value BETWEEN 14 and 16
                      )

Otherwise you will need something a bit more complicated:

    SELECT item_id
      FROM item
INNER JOIN form 
        ON item.form_id = form.form_id 
INNER JOIN attribute 
        ON item.attribute_id = attribute.attribute_id 
     WHERE item_id IN (-- here I select for Laptop and Screen Size dimension
                       SELECT item_id 
                         FROM item
                        WHERE form_id = (-- but first I need the form_id 
                                         -- for Laptop
                                         SELECT form_id 
                                           FROM form 
                                          WHERE form_name = 'Laptop')
                          AND attribute_id = (-- then I need the attribute_id 
                                              -- for Screen Size
                                              SELECT attribute_id 
                                                FROM attribute 
                                               WHERE attribute_name = 'Screen Size')
                          AND item.value BETWEEN 14 and 16
                      )