SQL Server Performance

User profile data - key valy pair or key per column?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by dotnet_demo@hotmail.com, Jun 4, 2008.

  1. Hello everyone,
    I am working on a project where I need to store some user profile information. We currently have two designs that we are considering and I was wondering if someone can help clarify the advantages of using one or the other.
    The first table design maps all the user profile properties to a column in a table as shown below:
    UserID Alliance PurchaseID Theme LastUpdate
    1001 001 5d25c Default 6/3/2008
    1082 009 9x98j Maroon 6/1/200/
    The secod design has only three columns and uses more like a key/value pair approach as shown below:
    UserID KeyName KeyValue
    1001 Alliance 001
    1001 PurchaseID 5d25c
    1001 Theme Default
    1001 LastUpdate 6/3/2008
    1080 Alliance 009
    1080 PurchaseID 9x98j
    1080 Theme Maroon
    1080 LastUpdate 6/1/200/
    Which of the two approaches are considered best practice or which one will be best performance wise? Thanks.
  2. Adriaan New Member

    The second design is only required when - for the same entity (UserID) - for the same attribute (Alliance) - you can have more than one value (001 & 009). This is part of normalization, where you identify repeating values.
    Sometimes this approach is used when the list of attributes for the entity is not fixed, or must be flexible without having to change the table structure. You gain flexibility, but for the average client application it is easier to interface with a normalized table.

Share This Page