Data Modeling

[zim_ulator]

For some months now, I've been working on a redesign of a system for a Non-Profit needing to track donors and donations. About two months have been devoted to spelunking, carefully exploring the labyrinthine artifacts, efforts of previous consultants, trying to make some sense of strangely "normalized" tables and relationships. Considering that the last labor of love for this beast of a database was a donation of six hours time from a supposed IT professional, I am not perplexed by my clients' confusion, frustration, and inability to use their data for anything useful.

Right, that's enough of that.

So, now I'm considering the entities Donor, Person, Organization. I spent some time on the net looking for material to help me in the process, and I came upon somthing called "Universal Data Modeling".

http://www.univdata.com/pubs.htm

was a good place to look for some articles about it. In my understanding, it's a set of , you guessed it... "universal data model" templates, and some conceptsthat go along with them. Apparently, the most effective way to learn about them and how to use them for your own applications as a wau to jump start development, is to spend around $300.00US for a book and a CD; or you can get a two book set with CDs for aboyt $500.00US. Since this is not a plug for anyone I will not mention the name of the vendor. In fact, since I'm not getting paid and my client can ill afford the expense, I decided to squeeze as much free info from the web as possible.

The thing that grabbed me was that there is a special way of thinking about people and organizations using this technique. If I understand correctly, Persons and Organizations will get their own tables, as would Donor. For purposes of my example, Person and Organization can be considered special cases of Donor. Donor is a kind of Party. A Party is an entity which has attributes of both a Person and an Organization.

Since both Person and Organization relate to Party similarly, we create a Party_ID which is the primary key for the Donor table, and matching Party IDs in Persons and Organizations. We also relate Persons and Organizations to one another with a compound key (Person ID and Organization ID).

Now we can say, "A Person and / or Organization becomes a Donor when a Donation (a new donation record is created) is made."

If this confuses you, you will understand how tenuous is my own grasp at this point. The reason I posted this was to ask:

1. Does anyone care to contribute to this thread who knows more about Universal Data Models?

2. To invite you to check it out for yourselves.

3. I found a truly awesome open source data modeling tool here: http://www.univdata.com/pubs.htm

I'm just starting to implement a new design, so I'll post more as things come together. Thanks to all of you great folks who've helped me learn so much.

Denis

 

[The_Doc_Man]

First, I both do and don't believe in universal data models. As is the case for ALL OTHER PROJECTS AND TOPICS, the availability of a universally applicable tool, model, or program depends on the deviations of your system from whatever is "industry norm" for that topic. I.e. if you had the simplest, most incredibly plain-vanilla situation, something developed for the "normal case" might be useful to you.

As an example of what can go wrong, I used to (long ago) work for a company that made petroleum pipeline control systems. There was, at that time, a U.S. (E.P.A.) standard for pipeline leak detection. So we implemented that algorithm. After making several systems each for 24 different customers, we had 25 different leak detection algorithms - having never ONCE interested anyone in the EPA standard algorithm. Everyone had their own bells and whistles.

I've recently seen a major U.S. Government personnel system where the "standard, off-the-shelf, universal data model" that won the contract was no more than an 18% fit with the actual requirements.

So take "universal data models" with a grain of salt. I'll post a second time on a different aspect of your situation just to keep the comments separate.

 

[The_Doc_Man]

Now, as to issues of who donates what to whom...

You must start with your business's rules on this subject. It DOES NOT MATTER what they are. What matters is that you must know them. Intimately. More intimately than your last several sex partners.

Start by defining entities in the model. Sometimes this means "people" or "organizations" but in your case, you might consider "donor" as an entity. The details become exactly that ... details. Then you have donors and donations. Since donors can give more than once, perhaps to more than one charitable fund, donors and donations are related but separate. The possible recipients of donations (donees?) are separate. A donation, in the form of a donor, amount, recipient, date record, might be adequate as the middle table in a complex relationship that includes:

donor-to-donation as one-to-many
recipient-to-donation as one-to-many

Now, what is the difference between a personal and organizational donor? They will both have addresses, phones, and points of contact. So the question becomes a matter of having a field in the donor table that says "Person/Organization" (maybe simply a yes/no field) and EITHER a Point Of Contact field for organizations or an Organization name for persons who happen to be the P.O.C. If we are talking tax issues, in the USA even an organization has a taxpayer ID, so one field serves all. Conflating Donors into separate PERSON and ORGANIZATION tables adds a layer of complexity that might not be required - unless your business rules say they MUST be kept separate.

So... you've got a DonorID and a RecipientID that are the prime keys of the respective tables and foreign keys in the Donation table. What else do you really need beyond that? (That was rhetorical. If you are really in bed with your business model, you can already answer that question.)

If in doubt, borrow a dry-erase board and get some sticky notes. Design your tables by writing the names on the board. Fill out some hypothetical records, one per sticky note, to populate the tables. Then, using paper and pen, make a donation. Follow the steps required to properly post the data. Once you have the data-flow mechanical operation down to a fine art, you are ready to do what you need to do.

If there are special cases involved, be sure that you simulate these cases before attempting to implement anything. If you want to save your own time, do an extensive design and data flow analysis FIRST. Professor Nicklaus Wirth (father of the Pascal language) once said that fully 80% of all programming problems originate with problems in data design or data flow design. He is yet to be proven wrong.

 

[Pat Hartman]

I have used the "party" concept in a number of databases. It is quite useful when different types of individuals or companies need to interact with the rest of the application. Having the party table to define the entity and separate related tables to hold info specific to a particular type allows for greate flexibility. Then by adding the concept of a role, you can use parties in various ways. It would be an excellent approach to take with your new donor system.

PS, I did some work a long time ago on Reader's Digest's Donor system (which btw was not a pretty sight).

 

[zim_ulator]

The_Doc,

Thanks for your input.

First, I both do and don't believe in universal data models. As is the case for ALL OTHER PROJECTS AND TOPICS, the availability of a universally applicable tool, model, or program depends on the deviations of your system from whatever is "industry norm" for that topic....

Ah, the old I both do and don't believe answer... my wife loves that one I was a FoxPro developer in a previous incarnation, doing work for the jewelry industry, and later the energy industry. My last gig, we used Tom Rettig's Office (TRO) to get us started. We still had to do the model from scratch; the work saved was mostly in implementation.

I've gotten about as far as I can with Universal Data Models without laying out some cash, so comments and opinions like yours are very valuable to me.

 

[zim_ulator]

Now, as to issues of who donates what to whom... You must start with your business's rules on this subject.

Thanks for your input about the business rules. I've got that part as tidy as possible now. The idea of many recipients related to many donors was considered. It doesn't fit with our requirements, and as you suggested, it would add uneeded complexity.

Thanks very much for your input. Getting another developer's POV helps with the forest and trees problem. You rock.

Denis

 

[zim_ulator]

I have used the "party" concept in a number of databases. It is quite useful when different types of individuals or companies need to interact with the rest of the application.... . Then by adding the concept of a role, you can use parties in various ways. It would be an excellent approach to take with your new donor system.

It sounds like I'm in good company in considering the concepts of Party and Roles. Thanks for your input.

Denis