mercredi 27 mai 2020

Best way to insert large form/checkboxes into sql table

Im looking for advice/best practice around inserting large records into a sql database(approx 250 columns). I currently dont have much in the way of code as Im reluctant to get building this when im still unsure of the best approach. Ive searched the web and found database tips but none seem to cover the types of records that i am looking to submit - mainly small records like order details.

Problem- I have an form that managers can use to complete reviews on their staff. Along with a few generic bits of into, the form has a 12 radio buttons where the manager rates a staff member. This is then submitted into a sql database via php. In the database I have 1 record for each review (therefore staff can have multiple reviews for them over a period of time). The company has decided to expand the review form, adding extra radiobutton questions but also a large number of checkboxes to get further insight if a staff member is scored down. In some cases there is up to 20 checkboxes for a question.

The results from the reviews will be pulled into overall table/page which will focus on number of records and results from the radiobuttons. However i'll also need to know checkbox results as well - for example the page would highlight that "Question X" has an average rating of 65% across all staff and the top reason for this being pulled down is "checkbox C". Staff members will also be able to view completed reviews.

My solution - The only real thing i can think to do is create a new sql table which includes a column for every checkbox option. This is ramping up the number of columns in the database to around 250. Everything I read about sql tables tells me that a table this size isn't a great idea but im not sure what my options are. Depending on how a review is completed around 70% of the columns for a given record could have a null value. Another option would be to create a second table that holds the checkbox details separately and then have a 1-1 relationship between the 2, but i not sure why / if that would improve the efficiency, especially as i might need to pulls both lots of data at the same time when producing results.

I feel im being vague so let me know if any other info is required however as explained im very much trying to work out the best way forward before i start coding. It may be that have a table with 250+ columns is the only solution so any tips/resources on making sure this is as efficient as possible would be a great help.




Aucun commentaire:

Enregistrer un commentaire