Monday, April 7, 2008

OOP: Properties and UserProperties

The Problem

I want to show a page with all the user-settable properties. Any properties that the user hasn't set should be shown but the value should be empty or null or some nice text indicating such. However, what is the BEST way to design this?

The Tables

I have a [Properties] table. I have a [UserProperties] table that has a FK back to [Properties]. There is only a row in the [UserProperties] table is the user has set one. If not set, no row. So back to what I want to show: in the user's account area, a page of all properties where any properties that have been set show that value, otherwise some text indicating the property has not been set. I'm going to go through every option to go from DB design to .Net code for the page that Wayne and I discussed on this fine rainy, sunny day.


Most obvious from a database perspective: T-SQL join (1 db call) that includes all properties so depending on how it is written, right or left join. The problems with this are that the NetTiers layer is table-based. So I'm returning the properties for the user, but if the [UserProperties] table doesn't have the record, then NetTiers can't make an Entity object for that record because it requires the [UserProperties] PK for creation.

All .Net

Most obvious from a .aspx perspective: N+1 db calls. First page is a list of all properties (1 DB call), with each row using the userproperties entity for that property (1 call for each property). Any property that doesn't have a valid object in the userproperties shows text saying the property is not set. I hate this approach. This means both SQL and .Net have to chug through unnecessary work. Yuk. Yuk. Yuk. However, I can write it quickly. Hmm, write it quickly but spend eternity hating what I've done. Or scratch my head some more.

Other Answers

Less obvious: Work with NetTiers to have a custom proc and custom c# code to get this working. This should be easy. I know the proc code and I know where to tie into NetTiers. However, I still have an entity without a PK. Hmm, I'll have to think some more.

Less obvious: I'm using NetTiers as a stand-alone Library so how about sub/super class (depending on your perspective). Add a custom User class to my library that does what I need to do with a Properties object and a UserProperties object. I don't see how this reduces the db calls.

So far, I don't like any of these but I have a low-traffic site so I'm going to opt for the All .Net answer until I come up with a much better answer. Perhaps the table design is the problem.

Any thoughts? Suggestions?

No comments:

Post a Comment