RFC Database Design: Independence to Business Rules? 1

[alfredjp]

ive read somewhere that

"I believe that a well-built database should be flexible to changes in business rules and that the data should be stored independent of those rules whenever possible. We all know rules change, and I don't like messing with a production database if I can help it. I would rather have flexibility in the model and trust my business tier coders to do their job."

---

in my opinion, this would be the way to go... unfortunately, most database designers now, have their design quite tied to the presentation layer business rules...

what are your comments on this?

 

[josephwalter]

I have first-hand experience with the difficulties that arise from building database solutions that are too specific to the problem at hand. These short-sited solutions often result in extensive rework, and they complicate what would otherwise be a simple database.

A database that is designed as generically as possible is earier to understand and maintain. One should develop and compare alternative solutions when designing a database.

I'm also an advocate using groups of people to design databases. Not only will there inevitably be alternative solutions, but also designers are forced to explain and justify the details of their solution - which sheds light on issues that would have otherwise been overlooked.

 

[lespaul]

Regardless of the business logic, when designing a database you should first identify the data you want to capture. After you have identified the data, the database structure can be developed. Then regardless of how the business logic changes, the data should still be captured and the database design is flexible enough to work with any logic.

Just my two cents!



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[skiflyer]

I see no other way to do it other the Leslie's suggestion. I've not yet the pleasure of working on anything excessively large, but even on the tiniest systems where people tell me, but hey we only need this so it could be done A,B,C... I request the system be implemented under those concepts, and it's amazing how often in my very short career it's panned out that a feature requested just weeks later would've been near impossible with business rule shortcutting.

-Rob

 

[alfredjp]

the problem is that most programmers who are doing the presentation-layer are so pre-occupied in making their coding easy, and simple, and for whatever reason; to ALWAYS request changes in the design.

an example is in one project, wherein i have to design the "middle" database for a certain OLTP system. the original system uses a CHAR(1) for the organization_level (under which company heirarchy does a certain department fall into) - which could have char values for '0','1', etc. etc.

my initial design is that i just maintained that field definition.

however, one programmer in the team reasons out that he has to do lots of comparisons in the query (using a scripting language); and that have it as an INT would be more easier on his code.

i reason out that
1. the INT is way to big (having 4 bytes) compared to only one byte for CHAR(1)
2. i feel that CHAR(1) is more efficient to INT in queries, etc.
3. an extraneous value like levels that are 123.
4. if i were to make constraints to the table, wouldn't that slow down transactions?

any comments?

 

[lespaul]

If the existing tables have the organizational_level field defined as CHAR, you are absolutely right, you should maintain that field definition in your table. Now, should the fiedl have initially been designed as an integer is a completely different question, and at this point, it is MOOT!! If the other programmer needs integers to compare, have him use the CONVERT function in his queries to change it. Besides, if he is using a scripting language (VBScript?), once the query is run, he can assign the organizational_level to a variable and do comparisons then.

Good luck!!

Leslie

 

[alfredjp]

i definitely agree with you... unfortunately, this guy has more IT experience under his belt that i do... and i need a way to convince him IT-speak... i mean, i couldnt just say "it is my design philosophy," right?

sometimes i argue that im maintaining the simplicity of the database so it could be flexible enough to accomodate changes in the business rules... *sigh*

if anyone knows of some sort of a "textbook-rule" to explain this, i would really appreciate it...

 

[CajunCenturion]

Tell him that you'll agree to make the structure change in the database so that his program will work, provided that he 1) gets every other programmer to agree that he will be responsible to fix any other program that breaks as a result of the database change.
2) Also, have him write a memo to send to all users of that database that the database will be down for however long it will take, so that his database change can be accomplished. If the user community is agreeable to the database being down for that period of time, the you'll make the change.

Once he fulfills both requirements, then and only then will you make the database structure change.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein