How do I dynamically store data in a database table that is mutable changes

Question

1.00/5 (1 vote)

See more:

I had a requirement given to me to be able to store data from an object whose properties can change i.e the number of the properties and the values of those properties. So at first it sounds crazy, but I said I am no genius so let me ask and find out. Basically there is a server system that has objects with properties where those properties can be added or removed, but I need to read those properties values and store them in a database table. Now I know that if I define the table I have fixed fields and a fixed amount. Removing fields, changing names will affect reports that are done after changes are made etc..
So how would I create a table that is dynamic and yet maintain my ability to work with old data as well as new data?
I am writing in C#, against a SQL server. either of the following database engines MS-SQL MySQL is fine.

Posted 1-May-15 5:00am

stixoffire

Add a Solution

Comments

Andy Lanng 1-May-15 11:22am

Cheat
Serialize the data you don't need indexed in the database

Otherwise you can create a properties table. You can just have "Name", "Value" & "Type" fields. I use sql_variant columns which can store any type and can tell you what type it is, but you don't need to. - Same issue if you want to index that data though

stixoffire 1-May-15 16:14pm

So what you are saying is a separate table with name, value, type and relate that to the "primary" table as additional fields so then I can do as they wish ? My DAL will need to incorporate a dynamic property as well.
Using the variant column - I can then just use 2 fields name and value and must make sure the right datatype goes in?
I have to say it sounds like some work in my DAL for me to implement this.

Andy Lanng 5-May-15 4:08am

The variant type is just a bit cooler than the extra column, but no more useful in the long run. I created a calculated column to get the datatype out (as it's a function on the variant column) and a stored procedure to work out what type I insert. It's a lot of work for very little data space gain.

My DAL is sql to linq and my partial access classes are very well organised so updates are very easy.

What is the issue with your DAL? How does it's existing structure cause difficulties. Maybe I can offer help there?

stixoffire 5-May-15 22:13pm

My DAL is more or less a hard code of the objects which need to be saved to the database, although I am in the planning stages of it, I had the option to use entity framework but it seems there is a learning curve there for me. I have not installed the entity framework tool , and am using sharpdev 5 to do this - it will do some entity framework set up but not really what I see online that people tend to use. [and I am sure there are good reasons the majority stick to a certain way of using that framework.] I thought about going to a very simplistic architecture just because the app is not super data intensive - and I am familiar with it.. doodads and can easily autogenerate the layer.
Any advice , hints etc is very much appreciated, I am somewhat of an intermediate coder - but very rusty; I am picking up after over 5 years out.

Andy Lanng 6-May-15 4:13am

The best thing about using an entity-framework db is that you can use a POCO approach (plain old CLR objects). It makes parsing data up and down the access layers very easy to implement and secure. Anyway, I can stick to your DAL method for the sake of this issue.

I assume you use strongly typed datasets (.xsd), or some sort of dataset model.

I think you will have to add a second table to the dataset with a relationship. If you go the POCO route you will need an 'extra field dictionary' or such, but a related dataset should work fine. You can add this in the design view of your .xds or set it manually in a generic dataset.

Let me know if you need some examples of how either of these options can be used and I'll write up a solution ^_^

stixoffire 6-May-15 10:38am

I have always used the POCO method for DB access, although I have seen XSD files for datasets - I never wrote them myself, they were generated by the IDE - I think when I was using Visual Studio and if my memory is any good here I think studio had a designer for them , just not sure. If you have an example of Which would most likely be easiest and best for me given my experience level and my rust, that would be fantastic.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)