0

I am creating a survey. It is long enough that I want to give people a chance to save what they have so far. I am wondering what the best practice is for saving the data. Do I turn off foreign key constraints so if they haven't selected everything yet then foreign key constraint errors are ignored. In this example I use an ID to link the documents table to the table that holds what they have selected. If they haven't selected a document yet then a -1 is inserted as a holder. Or do I create a second table to hold the saved place data. Or is there a third option.

Jeebwise
  • 177
  • 2
  • 2
  • 12
  • 2
    Insert default values for questions not answered and mark the survey as incomplete ? – Shashank Shekhar Nov 05 '15 at 15:39
  • You shouldn't disable FKs, instead you need to make those items that are not mandatory nullable in your database. – Paddy Nov 05 '15 at 15:42
  • I do make them nullable. DocumentId in the event table is linked to the DocumentId in the Documents table and that is why it is throwing an error. Shekar idea is so simple I nearly slapped myself for not thinking about that option – Jeebwise Nov 05 '15 at 15:44
  • I go with Shekhar. You allow nulls mark the survey as incomplete, and when user finsh the survery you validate the whole form and update to complete – Juan Carlos Oropeza Nov 05 '15 at 15:45
  • Depends on how you save the data. If it's improperly normalized and you have a column per answer, well ... then you have different problems. – CodeCaster Nov 05 '15 at 15:46
  • That's pretty much exactly what nullable columns are for... When you want to save a record but don't have all possible data for that record. Just make relevant columns nullable. – David Nov 05 '15 at 15:47

2 Answers2

0

There is a 3rd option. You can generate the primary key right when the user begins the survey. There are two ways to do this:

  • Generate a database record and read-back the primary key (assumes it's generated by the database)
  • Change the primary key to be a GUID and simply generate a GUID in code.
Jared Dykstra
  • 3,596
  • 1
  • 13
  • 25
  • I do grab the generated key right away for the overall event. It was just the little things like the document. If it wasn't selected then I got a foreign key constraint error. – Jeebwise Nov 05 '15 at 15:42
  • 1
    Foreign key constraints can be nullable. So, if you don't have a certain relationship, just set it to null. (Ensure the column is defined to allow nulls) – Jared Dykstra Nov 05 '15 at 15:44
0

OK, using -1 this way means that you have to have document in the documents table with an id of -1. If you don't a better structure would be to define the field as allowing nulls. Then you pass in a null value.

You might want to read this: Can a foreign key be NULL and/or duplicate?

Community
  • 1
  • 1
HLGEM
  • 94,695
  • 15
  • 113
  • 186