What's the dividing line ...
... between a referential integrity issue and a business logic issue?
Assume tables A and B with fields A.RefId and B.RecordId. There's a foreign key relationship from A.RefId --> B.RecordId.
Let's say the values of A.RefId need to be further restricted: only values of B.RecordId where B.TypeId = 3 can be used.
Is this a business logic issue, or a referential integrity issue?
If it's business logic, does it have any place in the database?
I'd be curious to hear what people think.
Assume tables A and B with fields A.RefId and B.RecordId. There's a foreign key relationship from A.RefId --> B.RecordId.
Let's say the values of A.RefId need to be further restricted: only values of B.RecordId where B.TypeId = 3 can be used.
Is this a business logic issue, or a referential integrity issue?
If it's business logic, does it have any place in the database?
I'd be curious to hear what people think.
posted by Ann at 12:59 PM
5 Comments:
1) Business logic.
You gave a simple example where B.TypeId = 3... What if you had more constraints on other things? Let's say 5-10 more (not uncommon in a medium to large app). That's a lot of complexity now explicitly tied to how you structure your data storage.
Just because you CAN do it in SQL doesn't always mean thats the best place for it.
PK->FK enforces integrity of the tables, not necessarily valid data... that's where business rules come into play.
2) Wherever your business logic is located. If your Business Rules are in SQL Procs then it's fine to be in the database.
Just IMO.
Some reading material...
http://stackoverflow.com/questions/601151/should-the-data-access-layer-contain-business-logic
Also, I thought of a good example last night (because I just hang out and think of SQL examples)...
TABLE Person {
PersonName
, DwellingType }
TABLE Pet {
PetType
, PersonName }
// Business rule example.
INSERT INTO Person "Steve", "Apartment"
INSERT INTO Pet "Cat", "Steve"
// Should error in business logic because "Apartment" DwellingType cannot have pets... Can/cannot create a record because of a specific value in a column(s).
// Referetial rule example.
INSERT INTO Pet "Cat", "Steve"
// Errors because a person named Steve doesn't exist in Person table (eg. cannot REFERENCE Person row because it doesn't exist). Should error in business logic too, but just in case we don't want stray cats just hanging around in our database...
I was thinking about this myself, on the way home yesterday, and it occurred to me that one characteristic of business logic (although it's by no means definitive!) is that it's mutable. You may originally say that each state in the US belongs to a single sales rep, but that's a highly mutable requirement that will probably be broken as soon as somebody lucks out and closes a big deal with a chance-met person in a state not assigned to him. The requirement that every order needs to be associated with a customer is not mutable.
PS: Do you indeed have a cat?
Indeed I do, but the "business rules" at my "DwellingType" dictate that I cannot. The nice thing about business rules compared to DB Schema rules is that they can be overridden if someone knows how (or is cool enough) =D
Post a Comment
<< Home