I have a database table of people which is quite basic name address age etc each with a unique ID "personID". Linked to that i have an EAV set of tables to store extra fields which are potentially different for each person. I've already got code to save and load records from the person table into a c# person object or an array of person objects. However i'm not sure how to handle the loading of the EAV records. I was thinking of adding an array to my person object called "properties" which would be an array of properties each with a "name" and "value". So for example i could code
PersonA.Properties[i].Name = "Age";
PersonA.Properties[i].Value = 22;
Loading a single object seems ok i could do 1 database call to load the details from the people table and a second call to load the properties or return 2 datasets with one call. However how do i get an array of people with each person having their array of properties. If anybody has any links to examples i'd be grateful. Also bonus points if you can give me tips on how to implement properties so i can do code like:
PersonA.Properties["Age"] = "22";
With SQL server, I agree with Marc about getting it into xml. Makes it easy to query.
To put the code into the format you want, I would use a generic Dictionary if the date is homogenious for that property and then transform the xml back and forth between that.