none
How I can let users to extend database schema? RRS feed

  • Question

  • Hi.
    Your help is very appreciated.
    I want to build an app that is basically a  client db.

    For that I am contructing a "client" class (that has - name, address, contact details etc...).


    However, I want to enable my users, to add properties to my "client" class WITOUHT needing to change  my code.
    i.e. some clients might require an "age" and "annual income" properties as part of the "client" class.

    Which design pattern(s) might assist me?

     p.s.
    I would like to use .NET(C#) to implement it .

    Thanks a lot
    Roy

    Wednesday, March 29, 2006 6:25 PM

Answers

  • Hi!

    All depends on level of complexity you ready to take. Here is choices:

    1. Create 10 (or 100) string fields and allow user to name them
    2. Work with database schema to declare new fields (programmatically add/remove columns)
    3. Create 2 tables
      1. list of added fields (FieldId & Display Name)
      2. keep ClientId, FieldId as primary key and string as value

    Cool way is to extend schema, but it have some more work to than others. If you need more help - ask.

    Wednesday, March 29, 2006 7:31 PM
  • Take a look at the following paper : http://www.joeyoder.com/papers/patterns/Metadata/metadata.pdf for some related patterns

     

    Arnon

    Wednesday, March 29, 2006 11:13 PM
  • I think you simply want a client table with generic information about each client, and then another table ClientProperties, which has columns for the property name, value, maybe datatype, and associated client ID.  Downside is if you wanted to use the same property for several clients you would have to retype the property name.  You could create a seperate table that lists property types and then refer to one of the types when assigning a property to a client.  I don't really think this involves any special design patterns other than ones you would normally use.  I hope that helps.
    Thursday, March 30, 2006 9:06 PM
  • A few weeks ago I wrote a very detailed response to a similar question regarding some of the issues that can come up http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=274811&SiteID=1&mode=1

    The design pattern you are looking for on the code side is most likely "property bag" its the db side where things get a bit more interesting as reporting can become a complete nightmare ... I often recommend a dual methodology approach to minimize the cost on reporting.

    Saturday, April 1, 2006 6:26 AM
  • have you thought about not using a database, but XML instead?
    Sunday, April 2, 2006 3:58 AM

All replies

  • Hi!

    All depends on level of complexity you ready to take. Here is choices:

    1. Create 10 (or 100) string fields and allow user to name them
    2. Work with database schema to declare new fields (programmatically add/remove columns)
    3. Create 2 tables
      1. list of added fields (FieldId & Display Name)
      2. keep ClientId, FieldId as primary key and string as value

    Cool way is to extend schema, but it have some more work to than others. If you need more help - ask.

    Wednesday, March 29, 2006 7:31 PM
  • Roy,

    I'm not sure that I understand what you are asking.  Are you saying that you want the UI to dynamically change upon the request of the user?  Would these new properties already exist in the database?  If you can elaborate one of us on the forum could more accurately make suggestions.

    Tim Murphy

    Wednesday, March 29, 2006 8:27 PM
  • Take a look at the following paper : http://www.joeyoder.com/papers/patterns/Metadata/metadata.pdf for some related patterns

     

    Arnon

    Wednesday, March 29, 2006 11:13 PM
  • I think you simply want a client table with generic information about each client, and then another table ClientProperties, which has columns for the property name, value, maybe datatype, and associated client ID.  Downside is if you wanted to use the same property for several clients you would have to retype the property name.  You could create a seperate table that lists property types and then refer to one of the types when assigning a property to a client.  I don't really think this involves any special design patterns other than ones you would normally use.  I hope that helps.
    Thursday, March 30, 2006 9:06 PM
  • A few weeks ago I wrote a very detailed response to a similar question regarding some of the issues that can come up http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=274811&SiteID=1&mode=1

    The design pattern you are looking for on the code side is most likely "property bag" its the db side where things get a bit more interesting as reporting can become a complete nightmare ... I often recommend a dual methodology approach to minimize the cost on reporting.

    Saturday, April 1, 2006 6:26 AM
  • Hi Tim.
    The UI should be changed dynamically to reflect the object.

    The new properties do not exist in the db. They are created by the user in run-time (apart from very basic properties).
    Saturday, April 1, 2006 9:51 PM
  • Hi guys.

    Thanks a lot for all the answers.
    It amazed me - how fast and how many people choose to help by posting an answer.

    Thanks heaps.
    I'm working my way through all the different scenarios.

    Cheers
    Roy

    Saturday, April 1, 2006 9:56 PM
  •  Hi Greg.
    I read your linked post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=274811&SiteID=1&mode=1 with great interest. After carefully thinking about solution, I came up with an idea - Id'e be interested to hear your opinion.

    The requirements:
    1.Allowing the user to dynamically add properties to entities (i.e. client, suppliers, staff, etc..) without ANY code or db modification.
    2. Saving data-type information for every property (so validation checks can take place)(i.e. property 'Birth Date' for Entity 'Client' must not be null and must be a valid date).

    Table structre that I'm suggesting:

    Entities table holds for every entity (i.e. supplier, client) it's entity type . The entity type is a unique indentifier that allows me to get all the properties for this type from Properties For Entitities table.

    Properties For Entitities describes all the properties for each type (it's many-to-many relationship  between  EntitiesTable and Properties Table.

    Object Data table is the one that actually holds the data.

     

    EntitiesTable

    Ent ID (pk)

    Ent Name

    Ent Type(fk)

     

    Entities Type

    Ent-PK (pk)

    Ent Name

     

    Properties Table

    Prop NAME (pk)

    Prop Display Name

    Prop Type

    Prop IsRequired

    Prop Default Value.

     

    Properties For Entitities

    Ent  Type(fk)

    Prop Name(fk)

     

    Object Data

    Ent ID(fk)

    Obj PropertyName(fk)

    Obj Preporty value

     

    This design allows me to:

    1. Dynamically add properties.
    2. Having data-type and defualt value for each property (i.e. I can create dynamic validation checks in GUI)
    However:
    1. The code is a bit more complex.
    2. Performance is not the fastest (can't use indexes efficiently).
    3. Doesn't handle situation of heirarchical properties, or properites with Business Logic.

     

    I hope I explained things clearly, if not please ask me.

    Thanks again.
    Roy

     

     

     

     

     

     

     

    Sunday, April 2, 2006 1:58 AM
  • have you thought about not using a database, but XML instead?
    Sunday, April 2, 2006 3:58 AM
  • yes.

    I think you right. storing the meta data (i.e. what properties each object supports), in an xml's db field is ideal.
    It overcomes the problem that relational db has with hierarchical structures.
    I think that this is the way to go.

    Storing ALL the data in an xml file (not using db at all), will have some major drawbacks:
    1. Concurrent  users (write) is not supported.
    2. Takes long time to load to memory (if using DOM).
    3. Does not have optimised search as good as db has.



     

    Sunday, April 2, 2006 5:47 AM
  • Why invent wheel? SQL server has stored procedures to read DB model (such as sp_columns) and to alter DB scheme ("ALTER TABLE" SQL command). What is the reason to build wrapper over SQL to do SQL's native job?

    Also in .NET, DataSet class have schema inside it. You can add new columns to tables or remove them from there. In DataAdapter class there is MissingSchemaAction field that help to deal with new fields.

     

    P.S. Roy-roy, you should mark 1 or 2 posts as answers, but not every post. This will help for people in future to find best answer on the question, without reading whole discussion.

    Sunday, April 2, 2006 5:59 AM
  • Hi Sergey.
    Thanks for your quick reply.
    If I understand you correctly, every object definition(customer, supplier), will have it's own db table. (all suppliers will be in suppliers table..).

    The way I can add or remove properties at run time is to modify the appropriate table.
    This method also allows me to define data-types and other attributes for each property.

    Did I understand you correctly?

    p.s.
    The reason that I used "Mark as answer" is because I thought that users get credit for answers on forums, and I wanted to show my appreciation. I apologise if that's the wrong thing do to.
    Sunday, April 2, 2006 6:17 AM
  • Yes, you get it right - when you develop database, you create columns in designer, when you want users to extend DB - let them make columns through your app. You can mark fields made by users with underscore or any other way, for example, user want to add BirthDate field to [Customer] table, so you extend DB with [Customer].[_BirthDate] field.

    Users do get credits by answers, but this is not the goal. Goal is to mark best answer (or two answers) so in future when somebody will search forum for help, he will see answer faster. I think nobody will be very upset if you unmark them all for now and when your question solved, you will mark best answer or write resume by youself and mark it as answer. BTW, I'm changing thread name to better reflect your question, to make it more clear when somebody search forums. If you think there is better name for thread - contact me or change it by yourself :)

    Sunday, April 2, 2006 6:37 AM
  • Thanks Sergey.
    1. One thing that this solution is missing is meta data on properties that are not part of the field's built-in db attributes (such as: null, default value, data type etc..).
    I.e.
    * What if I want to specify for each porperty "order" attribute (it will determine the order in the GUI) or
    * inactive attribute (if user chose to  'get rid' of property that already has some values in it and I want to keep the data).
    * What about parent-child / hierarchy relationship between properties?

    therefore, apart from the entities tables(customer, etc..) I can use "metadata" table which holds this data.

    METADATA table
    Property ID
    Is Active
    Order
    Parent Property ID.


    * Thanks for explaining about the "mark as answer" thing.
    I feel that it might be unfair to unmark already marked answers now. I will do it for future posts.



     

    Sunday, April 2, 2006 7:31 AM
  • Yes, extra information (Visible, AppearanceOrder) may be stored in some own table and it can be good not only for custom fields, but for all fields in solution. Perhabs there is ability to extend fields metadata in SQL, but I don't know this yet.

    If you talking about parent-child relations... Hmm. In fact this will increase complexity of your app, if you make it for typical user, then I doubt this will be good feature (for example, how many Excel users know how to work with Excel? In my practice they often don't even know how to navigate to the cells). But if you need this - you can create relations and tables in your DB. But apparently you will fall into building another SQL database designer. Sometimes it's more appropriate to use other apps, like InfoPath, but this is hard to tell - all depends on user task you are solving.

    Sunday, April 2, 2006 8:07 AM
  • "you can create relations and tables in your DB. But apparently you will fall into building another SQL database designer"
    Not neccessarily, I can just add another field "ParentID" to the properties table, which holds the property ID of the parent of the current property. So it's pretty straight forward. But one thing to take under consideration, is that MS SQL SERVER (at least the 2000 version) is not very good(performance wise) with recursive queries.



    Sunday, April 2, 2006 8:53 AM
  • But why you need such property nesting? What kind of app you making? Will users be smart enough to use it?

    You say SQL 2000 have bad performance in recursive queries, can you share some numbers here? I want to understand SQL quality things a little more. I heard from people that SQL 2005 is really slower and waste more resources. One my friend says that his company writing own simple database engine, because SQL is not good for their solutions. After I reboot I don't use SQL 2005 yet, but it already takes 80MB of RAM. Actually I have not too much running apps (most of them from MS), but Windows already takes 720MB of RAM. Soft becomes more expensive and resource hungry. I'm afraid to think about Vista's appetite... So what numbers do you have in SQL 2000 that disappoint you? How big is database, how many users, etc.?

    Sunday, April 2, 2006 9:32 AM
  • "But why you need such property nesting? What kind of app you making? Will users be smart enough to use it?"
    Probably not in the very short term, but I want to make the design flexible as much as possible, so that the design can cater for future requirements (as much as possible).

    "You say SQL 2000 have bad performance in recursive queries, can you share some numbers here"

    I tried to find on the internet some perfromance tests for SQL SERVER with nested queries but not luck. I will run a test myslef, and let you know the outcome (I'll comare it with MySql).

    After I reboot I don't use SQL 2005 yet, but it already takes 80MB of RAM.

    Actually 80MB of RAM is not lots for SQL SERVER. I work with SQL SERVER 7, on my db server, I am running 5 dbs with 36 concurrent connections, and it uses 2.5 Gigs of RAM!!
    I was amazed, but after a research on the internet, I found that:

    1.  SQL SERVER 7(maybe 2000 as well) is a  memory hog.
    2. Therefore it is recommended to run SQL SERVER on its own machine.

    Sunday, April 2, 2006 8:00 PM
  • Making flexible design is really good, but not welcome till you really need it for solution you writing. It's not hard rule that everybody must follow, but I'm personally like XP methodology, where it's said that you should create only architecture enough to solve current problem and not waste time on unspecified probably future requirements.

    About SQL - 80MB just after start when no DB were connected and opened yet. SQL 2000 also like to manage memory by very complicated rules and like to take almost all RAM it can reach (probably this is "great", but I remember case when company's server was really overloaded just because SQL took 100% of RAM, decreasing it's usable region to 90% triple the performance). I have standalone development machine, it has 2GB RAM and it's twice more than enough to start system and develop apps without even falling to memory swapping. I just don't like the fact that SQL use so much memory without reason.

    If you interested in performance subject - you can go and support my undertaking here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329219&SiteID=1

    Sunday, April 2, 2006 9:30 PM
  • I will try to find a site that explains this better.  For the heirarchies you can do better than just a ParentID and recursive queries.

    Define the table like this:
    PropertyID
    <property detail fields>
    PropertyParentID
    PropertyLeft
    PropertyRight

    Then your queries become MUCH easier.

    [EDIT]

    Found the site: http://www.sitepoint.com/article/hierarchical-data-database

    Sunday, April 2, 2006 9:35 PM
  • Roy,

    This is a good generic solution for being able to add properties, on your classes you could then use a property bag for accessing data.

    As I said in the other post the real issues come in when you look at scalability and reporting. XML columns come with the same issue (minus the duplication of effort for data typing etc).

    Generally the attribute table as you describe it is a well known solution ... however be very careful when you use it as it does come at a cost and if this cost is not returning necesary functionality .. the cost should be very well thought through.

    I mention in the other discussion that the attribute table can also have a very effective use in telling you the functionality that your clients really want added. As an example, if you start to see a gratuitous amount of people adding "Birthdate" as an attribute, serious thought should be given to moving it to a typed column as you will be able to index on it and it will make reporting a much nicer experience (they often want horizontal views when they look at such data).

    As for the XP discussion, you could quite easily build this into a reusable system library which would allow you to extend any object in any of your systems.

    Cheers,

    Greg

    Monday, April 3, 2006 7:15 AM