Tech Off Thread

22 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

DB Design--User-defined Fields

Back to Forum: Tech Off
  • User profile image
    Larsenal

    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?

  • User profile image
    Maddus Mattus

    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.

  • User profile image
    TrainingTime

    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.

  • User profile image
    blowdart

    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.

  • User profile image
    Maddus Mattus

    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.

  • User profile image
    blowdart

    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 Expressionless

  • User profile image
    JohnAskew

    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.

  • User profile image
    blowdart

    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.

  • User profile image
    JohnAskew

    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.

  • User profile image
    blowdart

    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.

  • User profile image
    Larsenal

    Helpful thoughts from all.

  • User profile image
    ScanIAm

    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.

  • User profile image
    blowdart

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


    How else are you supposed to look uber 3l1t3 Smiley

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

  • User profile image
    Maddus Mattus

    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.

  • User profile image
    JohnAskew

    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.

  • User profile image
    Larsenal

     

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



    How else are you supposed to look uber 3l1t3 Smiley

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

    I think it'd be between 20 and 100 in most cases.

  • User profile image
    Larsenal

    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.

  • User profile image
    MarkEwer

    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.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.