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?
Sure. I've done this successfully 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.
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.