>Hi Evan!
>
>I just read most of the messages in this thread and have to disagree with most of them. But rather than answering individually, I'm starting a new branch here...
>
>First of all, I want to stress the fact that this is a very important question that has to be answered very early in the design cycle in order to guarantee flexibility and a clean architecture.
>
>Also, I want to point out that what we call referential integrity in Visual FoxPro has historically grown into what it is today, but doesn't really fit the n-tier model. VFP mixes all kinds of data and treats it the same way, no matter whether business logic or technical issues are the cause for certain design. I think this is why so many different opinions come up here...
>
>Let's have a look at a couple of different examples:
>

>
1. Let's say we have a customer table and a phone numbers table. There is a 1:n relation between those tables, so the customer can have an unlimited number of phone numbers.
   >Now let's say we want to delete the customer. Of course, we also have to delete all the phone numbers for this customer. So where do we put the logic for that? I say it's the data tier. Here's why:
   >The fact that a customer has many phone numbers is a natural. If we weren't to think about customer data in terms of tables (which is a very FoxPro (relational data) specific thing to do) but "entities", the customer table and the phone numbers table would be one entity and the fact that it's stored in two different tables is a VFP specific implementation issue. If we were to move to another non-relational back end (such as XML or maybe an object database), we wouldn't have a 1:n relation here. The customer would simple have sub-nodes or a phone number collection instead of a second table. Therefore, if the business logic tier was trying to delete phone numbers, it would fail miserably (as the data would be automatically deleted), rendering our app inflexible and useless in that scenario.
   >
   >
2. Now, let's assume we have invoices for the customer above. Invoices have line items and other data we would build using child tables in VFP. However, let's not think about this fact for a moment, and just consider the invoices another entity just like the customer (for simplicities sake).
   >Let's say we delete a customer. Of course, we also have to take care of all the invoices now. Where do we do this? I say in the business logic tier! Here's why:
   >Invoices and customers are two different entities all together. The fact that they are stored in different tables isn't a VFP specific implementation issue, but a major design decission (and very much according the the real world). When a customer is deleted (or dies in the real world), invoices can still exist. There is no technical reason why this would be wrong. I'm sure lots of business still have invoices of people that died on file. However, it strikes us as wrong because of logical reasons. Logical reasons that are business related. For this reason, this part of the RI belongs into the logic tier.
   >Now what happens if we switch to a different back end (and I always like to use XML and object databases as an example here because they use an entirely different paradigm for all of this...). In XML, invoices would be a separate node (most likely) and in object databases, they would be seperate objects (all related to each other, yet seperate entities). Therefore, we need to remove them by hand, and this can only be done in the business logic. If it were in the data tier, we would have to rewrite such substantial pieces of business logic for technical reasons, which is plain wrong.
   >

>So the point I'm trying to make is simple: Think of your data in terms of "entities" rather than "tables". This helps to see the details and differences, and it is a more non-technical point of view which helps to separate the business logic from the underlying data technology.
>
>To make a long story short, here's my conclusion:
>
>Inner-entity RI belongs in the data tier, everything else belongs in the business logic tier.
>
>Markus

Hi Markus,

Your last few posts have been excellent... really made a light bulb "turn on" for me. Another way to put your last point might be

Intra-entity RI belongs in the Data Tier, Inter-entity RI belongs in the Business Logic Tier.

My question is, though, what do you call an "entity" - that is, where do you draw the line? For example, it seems clear that phone numbers are attributes of Employees, i.e. part of the same entity. However, Employees are part of a Company, which may be part of a Conglomerate, which is part of the NYSE, etc. etc.