Enforcing Referential Integrity with Business Objects

[JoeBuntu]

I am working on a mysql application to perform basic inventory management in a warehouse/production setting.

 

Question: I have a table of parts with many tables in my database linked to this table. If the part exists anywhere else in the database I don't want it deleted. For this application I am going with a Business-Objects approach. I am using classes to represent each part. What would be the best way to enforce this kind of referential integrity in my business object (or maybe outside of business object) with there being so many tables tied to this table?

 

Here are some of the choices I have come up with:

 

1) Let the database handle it for me and use the database exception to tell the user something like: "Can't delete this part or make this change because of [insert reason]"

    Reasons I like this: seems easy, simpler to handle concurrency problems

    Reasons I don't like this: 1) seems to go against good app design

                                      2) I would like to be able to list reasons to the user indicating specific reasons why a deletion is not allowed ("Part is in current inventory and Bill of materials"). I'm not sure if this method would allow me to do so.

 

 

2) Query every table (possibly a stored procedure) that is linked to the part to see if there is any referential integrity issues- keep the results as properties of my class.

      Reasons I like this: 1) It would be nice to have this in with the class so that

      Reasons I do not like this: 1) The query would probably become expensive as the database grows.

 

 

3) Some kind of transaction scheme where when the part is used by one of the tables then a transaction occurs. I'm thinking of a table like this: (partID, table, IO_Code) where the IO_Code would be a positive or negative one depending on whether the part is being used somewhere in the database. If the sum of the IO_Codes is zero then the part is OK to delete, the tables using the query could use be found easily using the same table.

      Reasons I like this: Doesn't seem that complex, I'm thinking I could use triggers in the various tables to write to the transactions table.

     

I appreciate any input on this, thanks

[Daniel0]

Assuming you use MySQL, the easiest (and best) would be to use the InnoDB data storage type and FK constraints.

 

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

[JoeBuntu]

I thought that relying on foreign keys would be easiest myself and they are already in place. I want to explore other alternatives because I would prefer that the end user would know immediately if there would be any constraints on deleting a Part Number beforehand instead of submitting the request to the database and then finding out it can't be done.

[Daniel0]

It is designed exactly for that purpose. The error for integrity constraint violations has a particular error code. You can look it up, match with a potential error you get when you try and then give a nicer error message to the end user.

[448191]

I didn't read the whole OP, but it doesn't seem problematic to me:

 

class A 
{
   private $_typeBObjects = array();

   public function delete()
   {
       if(count($this->_typeBObjects))
       {
           throw new RuntimeExpection("Can't delete instance of A because it has composite dependencies");
        }
    }
}
class B
{
   private $_typeAObject;

   public function delete()
   {
       if($this->_typeBObjects)
       {
           throw new RuntimeExpection("Can't delete instance of B because it has a composite dependency");
        }
}

 

Of course this is assuming an Active Record-like business object, a pure Domain object wouldn't have a delete method. You would have to do these checks at a different opportunity. It's very simply just defensive programming. No need to overcomplicate.