storage of arbitrary attributes and their names

Tech-Archive recommends: Fix windows errors by optimizing your registry

(I've done a bit of research on the web to try to track down an answer
to this, but I have not managed to find anything conclusive on the
subject. Perhaps I'm not selecting the right search terms.)

For the system my development shop is currently working on, one of the
database entities can have arbitrary, customer-specified name/value
pairs. We didn't want to encode these directly into the database
schema, so we were looking to use a table to store the name/value
pairs.

For a particular customer, though, a particular name might appear a
large number of times, perhaps many millions depending on the size of
the customer. As such, we're concerned there might be a bit of
unnecessary duplication going on in the database and wondered if it
made sense to have a separate table to store the names and refer to
them by foreign key in the attribute table.

However, we're also concerned that name table might become a bottleneck
as viewing code was constantly joining against the table to lookup the
information and insertion code queried and possibly updated the table
to ensure names were present.

Does anyone know what the time/space tradeoffs for this are with SQL
Server? Are there specific settings that would optimize either
approach?

.

Relevant Pages

  • Re: OODB vs RDBMS
    ... version of RDBMS. ... If you want to store several email addresses of one ... things work in relational database has proven to work very well ... the concept of a schema, and every table should belong to a schema. ...
    (comp.lang.python)
  • Re: Isolating Customer Data
    ... That is usually the easiest way to manage it when you need to have separate ... >>> customer data from each other. ... We have a system that will need to store ... >>> database for the application, this would store information about each ...
    (microsoft.public.sqlserver.security)
  • Re: Isolating Customer Data
    ... > What exactly do you mean by "Physically separate"? ... >> customer data from each other. ... We have a system that will need to store ... My initial thought was to create a central database ...
    (microsoft.public.sqlserver.security)
  • Re: Data-aware GUI controls
    ... > schema the application is most likely to change. ... These types of applications access multiple, ... > database schema is the same. ... >> value to the customer. ...
    (comp.object)
  • Re: Isolating Customer Data
    ... relatively easy to store the object code in a central location and then ... you mean by "Physically separate"? ... > customer data from each other. ... My initial thought was to create a central database ...
    (microsoft.public.sqlserver.security)