I want to build a "double recursive" database for ORGANIZATIONS and CONTACTS where
1) An organization can have zero or more child organizations (linked recursively to the same table) (HQ, Div Office, Branch Office, Dept, etc.)
2) Contacts can have zero or more child contacts (supervisors - linked recursively to the same table)
3) phone numbers can be assigned to an organization OR to a contact
4) contacts assigned to an organization would by default have the organization's phone numbers

Once I get the phone part worked out -- I want to tackle addresses and electronic communication (URLs, email and IM)

Has anyone seen an example of what I am trying to accomplish?

Thanks in advance.

Sure. I've done this succesfully by combining People and Organizations into a single table called "Entities". You can then link phone and e-mail records to this table. I also create it as a binary tree, allowing businesses to have departments and sub-departments while simultaneously mapping the organizational hierarchy of employees. The schema also allows each Business Entity to have a foreign key to the Employee Entity that is in charge of it, and each Employee Entity has a foreign key to a Business Entity which is there primary place of work.
For those inexperienced with database design, writing sql for this might make their head spin, but it actually models the intersection of the business environment with the employee environment quite nicely.

Why a single table?

Is there any way to upload schemas -- for "show and tell?"

Bob

Keep in mind that a recursive table is not the easiest to work with. I designed a couple of them and extracting data, creating queries, etc... can be a BIG pain. If you're determined to do it, I'd possible start with a very simple dataset to get the hang of it. I'm not saying don't do it, just that they are not easy to extract data from and you might want to reconsider the ease of working with your data.

The learning curve may be steep, but it is not tall. Once you get the hang of writing SQL for recursive tables you will find there are just a few techniques you use over and over again.
The key thing to keep in mind it to AVOID RECURSIVE CODE. True recursive code (where a procedure calls itself) is allowed (at least in TSQL), but it is not efficient for set-based processing and may be limited by the server in the number of levels it can ply. Instead, use a temporary table to accumulate your result set as you loop through the hierarchy.

And remember: "To understand recursion, you must first understand recurstion."

Amen!

Recursive tables tend to drive people wild until they finally "grok" them, then they become beautiful things. The problem is that most people don't cope well with recursion until they have some experience with it.

The reason behind making business units and people into a single entity is that you really need to get your head around the idea that from this point of view they really ARE the same thing. Blindman has just abstracted the concept of the entity, much like American law does, allowing corporations to behave so much like people that there is no practical difference execpt at the extreme edges. Using Blindman's model, anything that a person can do or know (object verbs and nouns, methods and properties), can also be done or known by a business. Any relationship that either can engage in, the other can to without prejudice.

An as the Blindman observed: "In order to understand recursion, you need to understand recursion". This sounds sillly, but it truly encapsulates the problem into a single, pithy observation!
-PatP

What I am beginning to understand is this--

I would create ONE table that would be the container of the names of the organizations or persons because these objects act alike.

Is it okay that these objects would have many blank fields – i.e. Organizations would not use the FName, LName fields and Persons would not use the CompanyName field – etc.?

Is this good DB design?

Is this normalized?

Bob

Good questions!

Based on the questions you are asking, I think that you need to stay a lot closer to a "concrete" or "real world" design, where your tables stay very close to objects in the physical world... This kind of design is a lot easier to think about and manage... You can tackle more abstract designs later, when you're a bit more comfortable with the abstraction needed to support that kind of design!

The design that Blindman described is pretty abstract. It requires a much higher degree of modeling and programming skills than a more straightforward "concrete" design would. Both designs are fine, and both are "good DB design", but Blindman's requires a lot higher degree of abstract thinking, which makes it a lot harder for the database designer and the programmers that use his type of design.

Normalization gets harder to confirm as the design becomes more abstract. The problem is that the abstract design can cope with lots of problems that the concrete design can't, and that level of abstraction makes it harder to understand and therfore lots harder to truly pin down how it is "normalized".

Based on what I know about Blindman, I'm confident that he sees his design as normalized. I'm betting that he could probably convince me too. I'm not sure how well he'd cope with a dogmatic "normalization purist" that tends to see things from only one point of view.

-PatP

For what it's worth, I had to import data from a recursive table (without documentation) and it was a total nightmare. My only input is to make sure it's well documented.

I'm not disagreeing with Pat, but in answer to your question there is no problem with have a few unused columns on a record. I've implemented this in two ways: either store the company name in the LastName column with a boolean column indicating whether the recordtype is Company or Person, or store the company name in its own column and use that column to indicate the record type.

These Data Models might prove thought-provoking :-
1) One entity for all Hierarchies :-
http://www.databaseanswers.org/data_...hies/index.htm

2) Employees :-
http://www.databaseanswers.org/data_..._employees.htm

3) User-defined Hierarchies :-
http://www.databaseanswers.org/data_...hies/index.htm

4) Vehicle Parts :-
http://www.databaseanswers.org/data_...ture/index.htm

These are listed under the heading of 'Hierarchies and Trees' in the middle of this page on the Database Answers web site :-
http://www.databaseanswers.org/data_models/index.htm

HTH

B.Dimple
Senior DBA

This may be too in-depth for this forum -- so I am really just hoping for a link to a book or online tutorial.

I think I've got the concept down for the recursive relationship for Organizations and Persons (we'll soon see...)

My other roadblock is thus: How do I create a form with ONE fill-in textbox field for the organization on it and THREE fill-in textbox fields for various phone numbers – with an option for them to click a form button that would take them to an area for adding more phone numbers.

Once I can see a working model of the hierarchical Orgs/Persons table AND phone number syncing up in a separate table – I will be well on my way…

Any book or online tutorial suggestions?

Thanks again,

Bob

Your phone numbers should be in a separate table with a foreign key to the Entities table.

For the mechanics of how to display this, you should post a new thread in the forum appropriate to your development platform.