Fog Creek Software
Discussion Board

Sharepoint 2003 Portal Services DB Design

Hi all

The past few weeks I've been messing around and figuring out how Sharepoint stores all its list definitions and data.

For those familiar with Sharepoint, you know you can create lists and either use default fields and column orders etc or you can create your own fields, mess about with the field order, visibility etc.

The way Sharepoint stores custom lists attributes and data however, is (to me anyway) interesting.

All the data gets stored in the userdata table that has columns such as the list id this entry corresponds to, the order of the item in the list. There is then about 150fields, from nvarchar1..64, int1..64, float1..32, sql_variant1..8 that the actual data for all the columns in that list item.

There is then another column in the Lists table that defines what data shall be stored in what column in said userdata table.

Now, I know that is confusing as hell and you probably won't get it unless you've had a look at the Sharepoint database.

My question really is, why did they do it this way. It seems somewhat against standard practice to flatten data out like that in a relational database. Is there performance benefits with indexing and stuff? I can see that there would some benefits in the ease of rendering data, but what else?

Thanks for ya time.

Dan G
Friday, August 6, 2004

what flattening is there? they're mapping many different 'schemas' onto one fixed 'schema', so they can have one table in the DB but make it look to the user like each list is in its own table.

Friday, August 6, 2004

Ok good point.

Basically, I'm implementing something similar and am going to use views for the individual "tables". I'm really just trying to understand any serious problems with this kind of implementation

Dan G
Friday, August 6, 2004

>There is then another column in the Lists table that >defines what data shall be stored in what column in said >userdata table.

So they are storing multiple pieces of information in a single column?  Is that the flattening out you are referring to? 

I once stored chunks of xml in a single varchar column, it made debuggung a nightmare and couldn't be queried using sql.

An alternative (and this would be the obvious relational db solution) would be to have a separate table that has list id, column name, order, userdata table column, etc. and a single row per column.

I guess if you don't need to query that list meta data via sql it is ok to flatten it.

Saturday, August 7, 2004

Biggest problem with metadata-driven DB design is that it's generally difficult to understand and design around.

BUT, it's flexible as heck and (provided the metadata manipulation isn't too convoluted) pretty speedy (since you optimize a few SP's and they're then all cached running against a fixed schema)


Saturday, August 7, 2004

Hi Philo

>>pretty speedy (since you optimize a few SP's and they're then all cached running against a fixed schema)

That's the kind of answer I was looking for :)

Anyway, I think I've got my head around the design now.

Thanks all

Dan G
Saturday, August 7, 2004

Make sure you have LOTS of comments and documentation (including diagrams and flowcharts). If you look away from it for more than an hour, it'll take you two to get back into it.


Saturday, August 7, 2004

This is why the API is your friend.  Look at the API, not what's going on under the hood.  Trust me on this - it's changed twice (that I've seen) in the betas, and I HAVE put money on the fact that it's going to change with SP1 (or whatever they call it to align the Sharepoints with ASP.Net 2.0). 

I know it's tempting to go looking in the database, I started out that way too.  Just move past it as quickly as you can.  You'll feel much better.

Unfocused Focused
Wednesday, August 11, 2004

*  Recent Topics

*  Fog Creek Home