Archives

Made2Manage Lacks Referential Integrity

I was surprised that some of the software packages I was recently testing for database comparison and writing faster queries failed to work properly with Made2Manage. When I investigated further, I realized why they wouldn’t work. M2M, to the best of my knowledge, completely lacks referential integrity.

What is referential integrity?

Referential integrity is the concept whereby every record that is in one table has a corresponding record in a related table. The dependent table contains a foreign key which corresponds to the main table’s primary key. This prevents a record from being added to one table that does not have a corresponding record in another table or being deleted from one table without also being deleted from the other.

An example to clarify: if the sales tables in M2M had referential integrity then the sales order master (SOMAST), which would use the primary key sales order number (fsono) and the sales order items (SOITEM) table would contain fsono as a foreign key to the SOMAST table. Therefore, you could not delete a sales order master record without also deleting all of the items. So, if you were to open SQL Server Management Studio or Query Analyzer and enter the following, and I highly suggest you DON’T do this, DELETE from SOMAST; all of the SOMAST records would be deleted without a single warning.

On more than one occasion, I’ve had to search various tables in my database and delete orphaned records, those that occur in one table and have no corresponding record in the related table. If referential integrity was implemented this should not happen again. If you ran the delete statement above, you’d have many orphaned records in your SOITEM table.

I imagine that Consona would argue that their integrity checks are done by the front end program, as well as through constraints and in their indexes. I asked a couple of M2M Support Techs, when talking to them about other things, about referential integrity. They had no idea what I was talking about. M2M is, of course, not the only vendor to operate this way. Technically there is nothing wrong with it.

Why does it matter?

  • I use database comparison software to detect changes between databases over time. For example, I’ll compare a snapshot of my M2M database to one directly after I have performed actions in M2M to determine what goes on behind the scenes. This is very difficult, because many of these programs use these key relationships to perform this analysis.
  • It would lessen the likelihood of data corruption if integrity checks were done both in the front end program and at the database level.
  • Queries would be easier to write, because some programs use these relationships to suggest table joins for you.

Have any of you found orphaned records in your SQL tables?

9 comments to Made2Manage Lacks Referential Integrity

  • scott

    I have on several ocasions found orphan records. In fact M2M support often has me check for these when I run into issues.

    While I agree that refriential integrety should exists, it is my guess that M2M is more concerned with issues and features that have a more direct pay back. Thier overall pholosify seems to be to write quick and dirty code rather then to spend the time and effert necessary for robust code. While there method is best for the short term, they don’t seem to realize the long term benifits of robust code.

  • Orphaned records are something I run into no matter what form of database system we’re talking about. I do notice quite a bit in M2M, but I have seen much worse with other systems.

    Consona’s apparent focus on short term rather than long term is likely due to the code they are using. Would you want to put a serious amount of effort into robust VFP code? I certainly wouldn’t. That sorry language should have died a long time ago. Oh wait, it did die a long time ago….It’s really more valuable for them to give short term fixes and quick new releases than it is to devote the time/effort/money into developing robust code for the Latin of programming languages.

    I have hopes (not too high, of course) that the .NET version will have a more solid and robust foundation that what we have right now.

  • Good post! I’ve heard people say that adding referential integrity inside the database slows the database server down, but hey, so does adding data. I love having double-checks inside the database engine to prevent something truly bad from happening. Programs have bugs, and adding referential integrity inside the database server is like Reagan’s quote: “Trust, but verify.”

  • Just today I had to call support because I had a duplicate record in the Job Order Master Table (JOMAST). These kinds of problems should not occur in a properly designed SQL database.

  • […] table with an order by clause. Once again, this occurs because in my opinion the M2M database is not properly designed. Here’s a quick walk-through on how I found it. Incidentally, the screen shots are from TOAD […]

  • […] take the opportunity to normalize the database (or at least significant portions of it) and enforce referential integrity while migrating to the .Net platform. I think they should drop VFP entirely, train users to use SQL […]

  • Ross

    I have just recently started getting into this program and was trying to figure out away to create an SQL query. For instance, one of our departments is unable to “clock off” jobs, ie. update the system with the quantity completed. What this means is I have to spend almost half a day:
    1. Going through this work center’s job cards & entering them in manually;
    2. Reconcile the quantities I entered against what was on the job card (we call them routers)
    3. If the job is completed or ready to be closed, put it in a stack
    4. Take the afformentioned stack, go through it again and “close” the jobs that have “0 waiting to move” from the last process (aka have shipped)

    With aroun 50+ jobs a day to do, this can take me 2-3 hours, and it seems inneficient. What I’m asking, is there some code I could write (or borrow? ha) that would simplify some of the process for me?

  • Ross, it’s hard to answer a question like that as a blog comment. Please contact me at david [at]made2mentor.com and I’ll see if I can point you in the right direction.

  • […] If Master Po had designed M2M the sorels would have a primary key consisting of Sales Order Number, Sales Order Line Item, and Sales Order Release. You can learn more about Primary Keys and Referential Integrity problems in M2M here. […]

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>