Expending structure through run-time entery

Question

I am trying to build an app structure with C# + SQL. I want to discuss if my idea is efficient and ask if you have a better way to do this: Let's say that I am building an information base to categorize the entities in sub categories with unique/common attributes. For example:

>Entities                   
    >Living             
        >Plants         
            >Spore Bearing Plants       
            *hasSeed - FALSE        
            Seed Bearing Plants     
            *hasSeed - TRUE     
        >Animals            
            >Vertebrates        
                >Cats   
                *hasTail = TRUE 
                    >Tiger
                    *averageLifeSpan = 23
                >Humans 
                *hasTail =FALSE 
                *averageLifeSpan = someInt  
                    >Human1
                    *name = george
                    *age = 35

Now, on run-time, I want my application to be able to insert a new structure when I introduce, say, insects under a new sub category on vertebrates level, and build unique attribute for example string chitinColor

My idea is: To build one db table for all categories, sub categories and species:

id  name    parentID
0   Etity   null
1   Plants  0
2   Animals 0
3   Vertebrates 2
4   Cats    3
5   Humans  3
6   Human1  5
7   Tiger   4
8   Spore Bearing Plants 1
9   Seed Bearing Plants 1

then a table for attributes for each data type

IntTable        
id  attributeName   entityID
0   averageLifeSpan 3
1   age 5

BoolTable       
id  attributeName   entityID
0   hasSeed 1
1   hasTail 3

finally, a value table for each data type:

IntValueTable           
Id  entityID    attributeID value
0   7   0   23
1   6   1   35

BoolValueTable          
Id  entityID    attributeID value
0   8   0   FALSE
1   9   0   TRUE
2   4   1   TRUE
3   5   1   FALSE

So that in run-time, if the user will introduce a species family or a new attribute to an entity, it can add up. Is this the most feasible way to do this? I see that it looks like a call for a XML file but I don't know a lot of XML operations in C#, though I am good in SQL. Thank you for reading patiently Regards

You should write a program that output the table to xml file and save it to somewhere. And load it in your program and parse it. — 鄭有維, May 09 '17 at 00:47
Thank you for your answer, I prefer not to use XML at this moment. — Rivendare, May 09 '17 at 03:26

score 0 · Accepted Answer · edited May 23 '17 at 12:18

0

The pattern you are considering is called Entity Attribute Value. Generally speaking, if you bring up EAV, people will mumble a curse under their breath. As an example, Magento (a common e-commerce platform) uses EAV for product indexing, and it causes problems so massive that third parties have written programs just to go through the EAV tables and denormalize them on a regular basis.

That being said, it has its uses. I think this answer attempts to weigh the pros and cons.

edited May 23 '17 at 12:18

Community

1
1

answered May 09 '17 at 00:47

John Wu

50,556
8
44
80

Thank you for your answer, I read the link carefully. Well, it is imperative for users to be able to put in new content on run-time. It seems like there is no alternative. – Rivendare May 09 '17 at 03:28

Expending structure through run-time entery

1 Answers1