so I have this application that has been running at work and collecting data from users for 3 years now. It is my first large php/mysql project but it started out fairly small.
It was initially just a simple list but now has evolved into what could potentially become a mission critical application.
Since the data in it stands to become more official in regards to my organization I've converted all of my tables to innoDB and I'm in the process of implementing Foreign Key constraints to many many of the tables in the database to get rid of as much bad data as I can.
this is my problem.. (in a nutshell)
Most all of my parent tables have ID rows that are auto incrementing. most all of my child tables that link to these parent tables have values of 0 in the records where a parent row isn't associated with that child row. These 0 values break the foreign key constraint and aren't allowed.
I could go through the entire database and update everything that has a value of 0 to be NULLl so as to not break the constraint but that means that i'll also have to go and update ALL of my sql code in the application to insert and update values of NULL instead of '0'. This wouldn't be a big deal but because my sql code currently looks like this..
Code: Select all
$sql = "UPDATE tblTable SET field1 = '$_POST[selType]', feild2 = '$_POST[txtName]' WHERE field_id = $_SESSION[id]";Code: Select all
UPDATE tblTable SET field1 = 'NULL', feild2 = 'Users Name' WHERE field_id = 565;I can see a number of ways this could have been avoided but thats all in the past now and I'm really only concerned about how to fix this problem.
I guess one of my options is to go and in every parent table create a row with key values of "None" and make that row's ID the default and use that ID where there are currently values of 0 or NULL in the table. I'm not really wild about this solution because it skews the data and gives a value to fields that should actually have no data in them.
What is the best way to handle this? What is the best practice regarting this sort of situation?
All help and replies are greatly appreciated!
Thanks
Will