Xaprb

ASP.NET’s Profile DB schema

with 6 comments

CREATE TABLE dbo.aspnet_Profile (
    UserId uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
    PropertyNames ntext NOT NULL,
    PropertyValuesString ntext NOT NULL,
    PropertyValuesBinary image NOT NULL,
    LastUpdatedDate datetime NOT NULL
)

select top 1 UserId, PropertyNames, PropertyValuesString from aspnet_Profile;

6 Responses to 'ASP.NET’s Profile DB schema'

Subscribe to comments with RSS or TrackBack to 'ASP.NET’s Profile DB schema'.

1. Normalization is a funny thing for me. I usually take it for granted, because OOP comes naturally to me. When I first took a databases class, I didn’t understand the first-, second-, and third-normal forms — I thought it was something you were supposed to know how to do by “instinct”, like preferring constants over “magic numbers” in a program. (This is not to claim that I do it right — just that I do it…)

   That being said, I’m not clear on exactly why this isn’t normalized. That’s no doubt because I’m not clear on what each column is supposed to represent.

   I’m not familiar with the PHP serialize function. But it looks like it stores the variable name, type, start, and length? So, “S” means string data and “B” means binary data, telling you which of PropertyValuesString and PropertyValuesBinary to look in?

   So I guess one solution would be to have UserID, PropertyName, PropertyValue, with a composite primary key on the UserID and PropertyName. PropertyValue would be binary data, interpreted as ASCII or image or whatever by the server code.

   

   Tim McCormack

   9 Jan 06 at 2:45 am
2. One of the nice things about the Profile system (as well as the Login, Roles and Site Navigation system) is that it is pluggable via a provider model. What this means is that you can replace the schema and back-end implementations with your own preference.

   We are actually going to be publishing a cool sample/implementation later this week that shows how to map the profile object directly against a table in your database. If you want to bind your profile against a normalized table, that might be the easiest approach to take — you should be able to just download it, updated your provider definition in your web.config file, and away you go (no code changes required). If you want to customize it further, you can either write your own provider, or take the source in the same and tweak it to map however you want.

   I’ll be posting a link to the new Profile download on my blog later this week once it is out: http://weblogs.asp.net/scottgu

   Hope this helps,

   Scott

   

   ScottGu

   9 Jan 06 at 9:54 am
3. Tim: one of the requirements of first normal form says separate bits of data need to be stored in separate columns. I should have linked to it before, but you can find lots of great explanations of normal forms elsewhere on the web.

   Scott: my main point was the default provider should be written differently. I’m not making the decisions and I don’t know what thinking goes into them, but if MS provides default functionality, it would be better for it to be more useful for the people who really need it, rather than just a “code sample” — which is what it feels like right now. The default implementation isn’t very useful for anyone building a serious site in this case. ASP.NET certainly isn’t aimed at script kiddies, so I’d expect it to ship with a Profile feature designed for sites that want to do big business. If the table schema were UserID, PropertyName, PropertyValue with a clustered primary key on UserID/PropertyName, and binary values encoded in base64 or something, that would be much more useful out of the box for a serious website.

   That’s just a suggestion — there are lots of ways it could be done. My point is, any serious enterprise needs to use that data in lots of places, not just in the ASP.NET code. I just can’t imagine a website not wanting to analyze it to capture metrics about user behavior, site and page effectiveness and so forth. That requirement is going to be mandatory up-front, so immediately the default provider gets ruled out, and you have to write your own. I know you do that reasonably easily, but I think you could help a lot of people not have to do it at all. Maybe the functionality you allude to will fill that gap nicely, but it’s not “in the box” with the rest of the ASP.NET bits.

   Thanks for commenting, both of you!

   

   Xaprb

   9 Jan 06 at 10:08 am
4. Hi Xaprb,

   Unfortunately there are two downsides with requiring that the schema be defined as a database table up-front:

   • Some data that you want to personalize is more “blobish” in nature and doesn’t map cleanly to a specific schema. For example, if you have a container web part control that is hosting user-controlled sub-web-parts, then you don’t know up front the exact data model that will be used (and in some cases it is impossible to model as relational schema).
   • One downside to requiring that you keep the database in schema sync with the profile object is that during development you end up having to change your database schema everytime you want to change the profile settings (which can be a bit of a pain). One nice thing about the loosely typed approach is that you can develop your app quickly, and then switch over to a table model once it is done (no code changes required — just swap in the new provider).

   Having said that — I do wish we had a table-backed profile provider in the box. Hopefully this web-download will be easy for people to find and use. There are no code changes to use it (just re-configure the provider) — so it should be easy to enable.

   Hope this helps,

   Scott
   

   

   ScottGu

   9 Jan 06 at 10:20 pm
5. Scott: thanks for providing more details. You have a very good point about developing the site, then switching over to a custom provider. I’m still not convinced that we’re talking about exactly the same thing, so I’ll draw some tables. Here’s how I might create the schema if I knew my requirements would never change, ever:

   create table aspnet_Profile (
       UserID uniqueidentifier primary key,
       FirstName varchar(50),
       LastName varchar(50),
       OtherColumns...
   )

   I think we’re on the same page so far. But if I were Microsoft, I might consider the following, or something similar, for the default provider:

   create table aspnet_Profile (
       UserID uniqueidentifier,
       PropertyName varchar(50),
       PropertyValue varchar(8000),
       primary key (UserID, PropertyName)
   )

   I’m waving my hands a bit because I’m not intimately familiar with ASP.NET’s internals, and I know there are probably lots of other factors I’m not thinking of. With the first schema, my data would look like

   UserID	FirstName	LastName
   [John's GUID]	John	Smith
   [Jane's GUID]	Jane	Brown

   That would be ideal if you knew ahead of time the exact data types and columns needed. If you don’t, which Microsoft obviously doesn’t when they ship ASP.NET, I think the second might be better than the current default. In that case, the data would look like

   UserID	PropertyName	PropertyValue
   [John's GUID]	FirstName	John
   [John's GUID]	LastName	Smith
   [Jane's GUID]	FirstName	Jane
   [Jane's GUID]	LastName	Brown

   Hopefully that makes my objection clearer. Microsoft can’t design the first schema, because they don’t know the requirements, but the second one might be possible, and might be superior. The schema is fully normalized, so things other than the Profile functionality could consume it. And then if you decided later to de-normalize into the first schema I’m proposing in this comment, it would be easy to re-code any stuff already written, migrate existing data, etc etc. The default schema presents some pretty serious obstacles to any of the things I just mentioned. Data migration alone would be a heck of a job!

   I haven’t really responded to what I think you raised as the main issue with this approach, which is “profile data is often a bunch of blobby stuff, not just neat name/value pairs.” I’ll admit that never crossed my mind! Is there a happy(er) medium?

   Thanks again for the lively and interesting discussion on this.

   

   Xaprb

   9 Jan 06 at 10:45 pm
6. i’m gettin crazy with this :(
   i’ve foud some articles but it’s a mad idea to download code to manage this creazy way to organize user’s table
   Coding it’s not getting easyer :O

   http://www.asp.net/sandbox/samp_profiles.aspx?tabindex=0&tabid=1

   

   Marco

   6 Jun 07 at 10:39 am

Leave a Reply