DB Design / Custom Attributes

[xAvailx]

I apologize ahead of time for the long post...

Background:
Working on a CRM type custom application. The application is for an
event management company. The company will provide the application for
other organizations to manage their own events. The events include
conferences, corp meetings, sales meetings, etc...
An event planner will define what information is needed for an attendee
to register for an event. We will be providing a standard list of
attributes for the event planner to select from. This list includes
personal information (name, address, phone numbers), air travel
information (preferred carriers, departure airports, etc...), hotel
information, etc...we've included all of the information available to
us from the business's previous experience. As far as the database
goes, all of the standard information given to use will be normalized.

The problem is each event may have unique information that needs to be
collected that is not part of the standard list of attributes. For
example, if McBurgers is planning an event, the event planner may want
to collect an attendee's McBurger employee code.

Depending on the uniqueness of the event, there may be up to 200 unique
attributes defined for it. This number comes from researching events
planned in the last 5 years. The number of attendees for an event range
from 100 to 10,000. The company expects about 3000 events per year.

Database Design
I've done a fair amount of research and found a couple of options to
meet our requirements, more specifically the need for event planners to
define custom attributes for an event.

1-)DynamicColumns:
Add an Event specific custom attributes table. The table would look
something like this:

Event_McBurger05
AttendeeID | McBurgerEmployeeCode | HiredDate | SomeOtherAttribute
-
[e-mail] | AxEt356 | 01/01/2004 | Other val 2

2-)EAV:
Add an EAV (entity, attribute, value) table. The table would look
something like this:

Event_Attributes
EventCode | AttendeeID | Attribute | Value
-
McBurger05 | ab*@abc.com | McBurgerEmployeeCode | AxEt356
McBurger05 | ab*@abc.com | HiredDate | 01/01/2004
McBurger05 | ab*@abc.com | SomeOtherAttribute | Other val 2

The Value attribute would be a character (probably varchar) datatype.

3-)Stronger Typed EAV
Have an EAV table for each data type. The tables would look something
like this:

Event_CharAttributes
EventCode | AttendeeID | Attribute | CharValue
-
McBurger05 | ab*@abc.com | McBurgerEmployeeCode | AxEt356
McBurger05 | ab*@abc.com | SomeOtherAttribute | Other val 2

Event_DateAttributes
EventCode | AttendeeID | Attribute | CharValue
-
McBurger05 | ab*@abc.com | HiredDate | 01/01/2004

There would be one Event_[DataType]Attribute table for each of the
datatypes allowed.

Pros/Cons

1-)DynamicColumns

Pros:
-Data integrity can be enforced
-Simpler queries for reporting
-Clearer data model for understanding data stored

Cons:
-Row size limitation of 8k must be managed (probably need to add
another table if run out of room.
-Stored procedures for CRUD operations would need to dynamically
created OR
Need to use dynamic SQL on the database or application.
-Adding/Removing columns on the fly can be very error prone

2-)EAV
Pros
-Static CRUD stored procs
Cons
-No data integrity
-Complex queries for reporting
-Worse performance than option 1.
-Table can get BIG...fast.

3-)Stronger Typed EAV
Pros
-Static CRUD stored procs
-Better data type integrity than EAV
Cons
-Complex queries for reporting
-Worse performance than option 1
-Table can get BIG...fast.
If you are still reading this...thank you!

The Questions:

-Are there other options other than the 3 described above? Or are these
pretty much it with slight variants.

-Does anyone see any missing Pros/Cons for any of the options that
should be considered?

-Is there a "preferred" method for what I am trying to do?

I suspect this will come down to the lesser of three devils. Just
trying to figure out which of the three it is.

We have prototyped the three options and are leaning towards option 1
and 3.

Any comments/suggestions are appreciated.

Thx

[Tzvika Barenholz]

Hi

What I have seen done in similar IT/CRM apps is the use of placeholder
columns.

i.e. in what you call the event table there would be on top of the
mandatory and key attributes, also ,say, 50 columns ,
custom1,custom2,custom3 .... custom50
they are all int type, and have a foreign key to another table:
table custom1values(id int, value varchar)

you have another table, called column_usage indicating which of the
columns are being used and what are their logical names.

the only problem with this attitude is that the report query to fetch
the data cannot efficiently be a join, but rather is a query first to
see what columns are in use, and then a join query using only the
necessary tables.

By the way, I am not sure if this is in your requirement, but this
allows the users of the system to add/alter custom properties as the
system runs. you give them a ui that defines column names and update
the column_usage table.

hope this helps
Tzvika

[xAvailx]

Good alternative.

This falls somewhere in between options 1 and 3. I like the idea of not
having to create columns dynamically. It still suffers some of the same
issues as EAV though:

Pros:
-Static CRUD stored procs
-Better data type integrity than EAV
-Easier reporting than EAV (will have to get user defined attribute
name from a MetaData table however).

Cons:
-Column sizes are static (same with EAV)
-Can't enforce column constraints for an event (same with EAV)
-Can run out of custom attributes (which may be something we can live
with as long as we allocate enough "place holders"...but at up to 200
custom attributes, that may be quite a few place holders).

Thanks for the input...really apreciate it.
Thx Tzvika.

[xAvailx]

I am "moving" this thread to the microsoft .public .sqlserver newsgroup
in hopes to get more replies...

Thx.