I have a design question regarding the best way to handle the following
situation for a custom CMS:
-we have multiple users
-each user has a private contact database
-in each contact database, the user has a contact list that consists of
both system standard fields (~15) (first name, last name, address 1,
etc.) and user specified custom fields (1-150+) (favorite color,
favorite team, last dinner date, "just about anything", etc.)
-these fields can be any data type and any size
-some fields could be multivalued (favorite color is red and blue)
-these fields could be added dynamically at any point by the user ui
without dba interaction
-user can potentially upload > 100,000+ records


We currently have two separate implemented solutions that we need to
merge into one platform. We've considered using both and creating an
access layer so that they appear the same to the rest of our system,
but it seems a bit too akward and apt for performance problems.

One stores the data in a vertical structure, so that each "field" is
actually a row in a table similar to (contactid, fieldid, value). This
currently gives the most flexibility for allowing the user to
dynamically add new fields on the fly. However, the query times on
this structure quickly become terrible.

The other stores the data horizontally so that each "field" is a
separate column in the table. We dynamically add a new column when a
user adds a new field. However, this can potentially hit the maximum
row size limitation (~8000 bytes) in sqlserver.


Given the above, what is the best data architecture to support this?
Let me know if you need more details.

Thanks for your time.

This problem is commonly called the OO DB impedance mismatch.
Basically, in the object oriented programming world, you can create
extremely elaborate interdependent models and create and destroy
objects in an instant. A database doesn't work that way.

Databases store data on disk. That is how a single server can store
terabytes of data. It doesn't load every single table into memory. It
goes out to the SAN or disk array and fetches only what it needs.

The database depends on metadata about tables, indexes, and query plans
-- to provide reasonable response times. The "vertical structure" you
describe is extremely powerful for a developer -- but it is meaningless
to the database.

I understand that in the real world, you give the customer what he
wants. I would go with the "horizontal structure". If you hit the
8,000 byte row limitation. Charge him extra money and create another
table and link it to first one.