DB Design--User-defined Fields

Follow
Oops, something didn't work.

Try again?

Sign In to be begin to follow this thread

What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.

Getting "follow" information

Stop following this thread

Start following this thread

What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.

Removing your "follow"

Setting up your "follow"

Did you know you can
sign up for email notifications?
- Subscribe to this feed

reddit

Tweet

Larsenal ready to give an answer

Jul 24, 2007 at 11:07 PM

Let's say, for example, I have a Person entity and a Book entity. The Book records are related to a person (eg, Book.fkPerson = Person.ID). You end up with each person having a list of related books.

In my application, I will allow user-defined fields. One person might want to add a "WebsiteURL" field to all his book records. Another user might want to add a "LoanedTo" field to all his book records.

Here's my initial design:
Person.ID
Person.FirstName
Person.LastName
...

Book.ID
Book.fkPerson
Book.Title
Book.DateCreated
...

CustomFieldType.ID
CustomFieldType.fkPerson
CustomFieldType.Label

CustomFieldValue.ID
CustomFieldValue.fkType
CustomFieldValue.fkBook
CustomFieldValue.Value

So when a person sets up their custom fields, they are adding/editing CustomFieldType records. When they view/edit their Book records, it includes a list of their defined CustomFieldTypes joined with the corresponding CustomFieldValues.

Is there a better way?
Maddus Mattus Maddus on C9, Is often controversial, But fun none-the-less -evildictaitor

Jul 25, 2007 at 1:33 AM

There are more then one roads to your goal, but it is for you to decide wich road is best.

Your solution is rather database intensive. You could chose to enter serveral predefined fields to the table.

Lets say:

int1, int2, int3, int4, int5
string1, string2, string3, string4, string5

etc.

Beware that your rowsize is maxed to 1024 bytes (not sure, but there is a max. SQL will tell you).

Then let your users define what they want to store where and save it in a configuration.

That way you dont need to join tables.

Hope this helps.
TrainingTime

Jul 25, 2007 at 1:50 AM

Mattus is right - and I have used that sort of system before. But as he points out you will then also need a way for the user to save what each extra field is called.

I've used a system like Mattus' when I myself can customise the field names for each user's system. However if the user themselves should be able to add, name and remove fields for their application then I would definitely go with your original suggestion.
blowdart Peek-a-boo

Jul 25, 2007 at 1:53 AM

Maddus Mattus wrote:

There are more then one roads to your goal, but it is for you to decide wich road is best.

Why intensive? Because of the joins? Databases are optimised for joins, your approach can be just as intensive especially on indexing because everything is one table.

The original approach almost describes a property bag; you'd have the base table, then a table with a list of property names and types (and an ID) and a value table containing the member ID, the property ID (compound foreign key) and the actual value.
Maddus Mattus Maddus on C9, Is often controversial, But fun none-the-less -evildictaitor

Jul 25, 2007 at 4:41 AM

blowdart wrote:

Why intensive?

query 1 table is faster then query multiple tables. no matter how optimised. Like I said it all depends.

If you have 100.000 rows with 10 extra properties and 100 users.

100.000 * 10 * 100 = 1.10^8 rows in your property bag.

or just plain 100.000 rows.

I think proven practice is the solution I presented. Just look at Microsoft CRM or Microsoft Sharepoint, it's built that way.

But I can also see the beauty in the presented solution from a programmers perspective.
blowdart Peek-a-boo

Jul 25, 2007 at 8:57 AM

Maddus Mattus wrote:

blowdart wrote:
Why intensive?

query 1 table is faster then query multiple tables. no matter how optimised. Like I said it all depends.

If you have 100.000 rows with 10 extra properties and 100 users.

100.000 * 10 * 100 = 1.10^8 rows in your property bag.

or just plain 100.000 rows.

I think proven practice is the solution I presented. Just look at Microsoft CRM or Microsoft Sharepoint, it's built that way.

But I can also see the beauty in the presented solution from a programmers perspective.

And what happens when you need 10 extra properties? Different properties per user? What you propose simply isn't expandable or customisable.

Mind you the worst example is the ASP.NET profile database
JohnAskew 9 girl in pink sweater

Jul 25, 2007 at 9:29 AM

blowdart wrote:

The original approach almost describes a property bag; you'd have the base table, then a table with a list of property names and types (and an ID) and a value table containing the member ID, the property ID (compound foreign key) and the actual value.

Property Bag?

Here's a CodeProject article on PropertyBag implemented in C#, but not as a database schema.

I found an article on SSWUG.org for a db implementation, as you described, kind sir, but I don't subscribe and cannot retrieve the article. Do you have a reference ICanDownload for property bag as a db schema?

Thanks.

blowdart Peek-a-boo

Jul 25, 2007 at 9:58 AM

JohnAskew wrote:

blowdart wrote:

The original approach almost describes a property bag; you'd have the base table, then a table with a list of property names and types (and an ID) and a value table containing the member ID, the property ID (compound foreign key) and the actual value.

Property Bag?

Here's a CodeProject article on PropertyBag implemented in C#, but not as a database schema.

I found an article on SSWUG.org for a db implementation, as you described, kind sir, but I don't subscribe and cannot retrieve the article. Do you have a reference ICanDownload for property bag as a db schema?

Thanks.

No; it's just one of the questions I ask at interviews

Consider the following awful ascii diagram;

Item
===

ItemID [PK]
ItemName
ItemCommonProperty1
ItemCommonProperty2

etc.

So pretty much like a base class would be. The problem arises in, for example, subclasses, each of which may add more properties, not good as you don't want to add each potential field to the table, that's not maintainable in a flexible manner.

So you create a Properties table;

Property
======

PropertyID [PK]
PropertyType
PropertyDescription

where type is probably the underlaying framework type, so you can validate

Then you need to connect an optional or extended property to the Item so you do

ItemProperty
=========
ItemID [FK]
PropertyID [FK]
Value

So creating a new extended property is a matter of creating a new Property in the Property table, and then creating an ItemProperty row for each instance of that property.

You can perform validation at the business layer, or in the stored procedure that attaches a property to the item. You can write a specific stored proc to create properties for a new subclass, if you're going that route, otherwise you will need to add/edit them one by one.

Then in the method that returns your property values, extend it to return a second recordset with all the properties for your object and pump them into your property bag implementation code side.

I've used this to great effect on a site which has around 2 million hits a day. You would probably want to cache the Properties table code side to make it more scalable.

JohnAskew 9 girl in pink sweater

Jul 25, 2007 at 10:59 AM

Thank you, blowdart.

Is there any re-thinking of the "Property Bag" design pattern, if you will allow me to call it that, that integrates .NET 3.0's dependency properties?

Is there a .NET 3.0 dependency property -based implementation for "Property Bag" or should I think of dependency properties as essentially the design pattern implemented in .NET?

Thanks.
blowdart Peek-a-boo

Jul 25, 2007 at 11:16 AM

JohnAskew wrote:

Thank you, blowdart.

Is there any re-thinking of the "Property Bag" design pattern, if you will allow me to call it that, that integrates .NET 3.0's dependency properties?

Is there a .NET 3.0 dependency property -based implementation for "Property Bag" or should I think of dependency properties as essentially the design pattern implemented in .NET?

Thanks.

Hmmm; never even thought about it, schemas can be very different to code patterns obviously, I guess you would put the dependancy problems that are on any object in the main table if they're *very* common, however the row size may get unwieldy. I guess what you're really after is data binding the properties? That would get more difficult as if you don't have them on the base table, returned in the first recordset I doubt they would bind. You could always project a view over the basic join to pull out dependency properties and make sure they're there, and properly named.
Larsenal ready to give an answer

Jul 25, 2007 at 11:17 AM

Helpful thoughts from all.
ScanIAm This is an...

Jul 25, 2007 at 11:39 AM

Larsenal wrote:

Helpful thoughts from all.

One thing to think about, too, is how <hatedword>Agile</hatedword> you want to be. Blowdart is correct that if you want to give your users complete freedom to add stuff to their hearts content, then the propertybag idea is pretty nice, BUT, there is an overhead.

If

1) you aren't being asked to actually make it unlimited and
2) there isn't a pressing need to do this and
3) you can safely assume that there is some limit (like: no more than 5 user defined fields)

then you may find that the propertybag option is overkill.

That doesn't make it a bad idea, but it is complex and may not be neccessary.
blowdart Peek-a-boo

Jul 25, 2007 at 11:41 AM

ScanIAm wrote:

but it is complex and may not be neccessary.

How else are you supposed to look uber 3l1t3

However the web site I was talking about had around 200 optional properties on the product object
Maddus Mattus Maddus on C9, Is often controversial, But fun none-the-less -evildictaitor

Jul 25, 2007 at 12:47 PM

blowdart wrote:

And what happens when you need 10 extra properties? Different properties per user? What you propose simply isn't expandable or customisable.

With all respect, what you are proposing is that you know the answer to a unknown question.

I am merely stating options.

Like I said, he has to make the decision. I don't know all the details. I think what if discussions are pointless.

A project manager once said to me: "Dont come to me with problems, come to me with solutions". I try to live by that credo.
JohnAskew 9 girl in pink sweater

Jul 25, 2007 at 12:57 PM

Maddus Mattus wrote:

A project manager once said to me: "Dont come to me with problems, come to me with solutions". I try to live by that credo.

I tried that on a high school math teacher once; it didn't work -- she kept giving me problems.

This does happen to be more of an "who knows how nutty they will want to be with these custom fields" scenario, so if I do not prepare for quantity, I'll most likely regret it. I'm most likely to go with the schema that blowdart revealed, this being the case.

What about you, Larsenal? Can you anticipate this for your system?

Since we're targeting .NET 3.0 & 3.5, I will want to exhaust the 'dependency property' features to see if they are the correct data structure to use on the code side... and how they might possibly alter the schema blowdart gave me.
Larsenal ready to give an answer

Jul 25, 2007 at 1:03 PM

blowdart wrote:

ScanIAm wrote:
but it is complex and may not be neccessary.

How else are you supposed to look uber 3l1t3

However the web site I was talking about had around 200 optional properties on the product object

I think it'd be between 20 and 100 in most cases.
Larsenal ready to give an answer

Jul 25, 2007 at 1:11 PM

JohnAskew wrote:

What about you, Larsenal? Can you anticipate this for your system?

At this point, I can keep the nuts at bay by preventing them from adding an insane number of properties.

My perf concerns are relatively minor considering the probable usage scenarios. We're not going to have 10,000 items with 200 custom properties each. It's going to be more like 50 items with 20 to 40 custom properties in a typical scenario.

The notion of "infinite" flexibility is nice, but is certainly overkill for what I need.
MarkEwer

Aug 11, 2007 at 3:32 PM

I have a slightly different idea for you that I used in an application. Instead of trying to anticipate the number of custom fields or creating a complex database schema that requires numerous joins I included an NTEXT column in the database and stored an XML fragment in this field. In this way, the user could define a new field and it became an element in the XML and the value of that field was stored as the value of the element.

Of course, the drawback with this option is the requirement to parse the XML before you can show the custom fields and the potential casting from XML to the native data type.

In the application I built this design turned out to meet our needs quite well. Essentially I just serialized/deserialized and ArrayList object into the NTEXT field for persistance.

Thread Closed

This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.

DB Design--​User-defined Fields

What does this mean?

What does this mean?

Thread Closed

DB Design--User-defined Fields