indexing pros cons in sql server 2008

Question

I am working on social networking site. now our team decide to store user profile in denormalized manner. so our table structure is like this

here attribute it means one fields for user profile e.g. Firstname,LastName,BirthDate etc...

and groups means name of a group of fields e.g. Personal Details, Academic Info, Achievements etc..

**

Attribute/Groups master - it creates hierarchy of groups and attributes.

**

Attribute_GroupId      bigint   
ParentId               bigint   
Attribute_GroupName    nvarchar(1000)   
ISAttribute            bit  
DisplayName            nvarchar(1000)   
DisplaySequence        int

**

Attribute Control Info - stores which control have to be populated at run time for the attribute as well as its validation criteria...

**

Attribute_ControlInfoId     bigint  
AttributeId                 bigint  
ControlType                 nvarchar(1000)  
DataType                    nvarchar(1000)  
DefaultValue                nvarchar(1000)  
IsRequired                  bit 
RegulareExpression          nvarchar(1000)

**

And finally Attribute Values where for every attribute , user wise values will be stored

**

AttributeId      bigint         Checked
IsValueOrRefId   bit            Checked
Value            nvarchar(MAX)  Checked
ReferenceDataId  bigint         Checked
UserId           bigint         Checked
                                Unchecked

Now they are saying that we'll create index on Attribute Values table. there is no primary key also there.

AS there's huge data going to be stored in this table. e.g. if there are 50 million users and 30 attributes are there it'll store 1500 million records. in this case if we create index on table, isn't Insert and Update statement will be very slow as well as at time of data fetching for one user. quires will also be very slow.

i thought one option for that like instead of attribute wise values i can store one XML record for one user.

so, please can anybody help me out to find out best option for this case. how should i store data?

here i can not make hard code table because at any time new fields can be added by administrator so i need some data structure where i can easily add any fields in user profile with 1-2 steps only.

please reply me if anybody has better solution for this.

Answer 1

You guys need a dba!

This is one of those EAV tables that is going to bite you down the road!

Answer 2

Check out those articles which highlight just how bad that design choice is, and what potential problems you're getting yourself into if you stick to that design:

• Five Simple Database Design Errors You Should Avoid
• Joe Celko: Avoiding the EAV of Destruction
• Bad CaRMa

It seems to be a fairly common design problem - and it seems like a good idea to programmers to solve it that way, with a attribute/value table - but it's really not a good idea from a database performance point of view.

Also:

Now they are saying that we'll create index on Attribute Values table. there is no primary key also there.

As some SQL gurus like to say: "If it doesn't have a primary key, it's not a table".

You definitely need to find a way to get a primary key onto your tables - if you don't have anything that you can use per se, add a column "ID" of type "INT IDENTITY(1,1)" to it and put the primary key on that column. You need a primary key! Database design, first lesson, first five minutes....

You need to rethink your design and come up with something more clever to store the data you need.

Answer 3

Bill Karwin (his blog) put together a SQL Anti-patterns PPT

• Link 1
• Link 2

He offers 3 alternate solution to EAV.

Indexing is the least of your worries...