I have been racking my brain for a way to handle this problem and am hoping for some advice on the best way to develop a workable data model in SQL Server 2000 that is still object-oriented and easy to query.

Assume an unknown number of heterogeneous objects that have an arbitrary number of relationships among any two or more object attributes (I have defined objects, their attributes and relationships, but do not have perfect foresight for our needs in the future). Examples:

1. A person can be an author, user, participant, employee, non-employee, etc (not mutually exclusive) and has: (a) address(es), email_address(es), institutions(es), etc.; (b) outputs, e.g., publications, presentations, etc. where each of the outputs has attributes that may be common or unique; (c) subordinate person(s); (d) access rights for themselves and their subordinates (at an object level); (e) demographic data; (f) can attend events (e.g., conferences, symposia, short_courses); and any other information that I may need to track (and will hopefully discover during the design process!)

2. Given the above description is probably poor, please permit me to give a more real world structure - a collection of vitae (for example, http://www.quintcareers.com/vita_sample_3.html)


A precursory literature review indicates that the consensus of the community is EAV approaches are frowned upon. Granted I am no where near an expert in this area, but I would tend to agree as it seems that ad-hoc querying in the domain of this model are time and resource intensive. Yet, by creating a table for each object I am not quite certain how to deal with querying attributes between the tables while preserving the object (and not having to create SQL blocks of equal complexity to the EAV approach).

As data warehousing would be the primary use for this data, easy access and manipulation is a must. Ultimately, my question is how do I maintain the benefits of EAV, yet maintain the ability for filtering the data without having to create an arbitrary number of self-joins?

Thanks in advance for any help.

As an aside, what have people found to be the best model for domestic and foreign addresses?

What you are discussing could probably best be described as a series of many-to-many relationships.

A person to their roles
A person to other persons
A person to their outputs (i.e. a book can have multiple authors)

Howwever, while it is possible for an address to have multiple people associated with it, this is probably a case where a 1-N relationship would be used.