CODEWITHSUNDEEP
c#sql-serverentity-framework
Sin5k4
Sin5k4

Reputation: 1626

Keeping unspecified data in sql

I have an entity (I use EF thats why, lets call it a table).I have a table and a child table that has a 1-N relationship with the main table.The sub table keeps the properties of an entity in main table.The properties are also kept as IDs and user defined with data types.FO example

Table A
ID NAME
1  ENTITY 1

TABLE SUB
ID TABLEAID PROPERTYID VALUE
1   1       1           5
2   1       2           SOMESTRING

PROPERTYTABLE
ID PROPERTYNAME  TYPE
1   PROP1        INTEGER
2   PROP2        STRING

So,a user has defined an entity with 2 properties with a integer and string data types.And I'll handle editing these properties in the UI with the proper components.

Now my main question is,what is the best way to keep these kind of undefined data in sql? (I'm not intending to keep blobs or images or big data)

Upvotes: 1

Views: 151

Answers (1)

Ronald
Ronald

Reputation: 2882

I'm still thinking about the question. What do you mean with keeping this in sql?

Your model is quite OK for storing unmodelled data.

One problem you have is the table spanning constraint that the value in SUB must match the TYPE in PROPERTYTABLE, which has to be checked. Another problem arises is you start thinking about mandatory properties or sets of properties (one implying the existence of another). That will give you a headache.

But as long as your application assumes that it can find any combination of properties or not find anything at all, it's OK. This assumption will either lead to only a limited number of properties, or the single properties being (more or less) unimportant, as is the case when you're storing remarks for some purchase order. (From the application point of view the contents and existence of the remarks is irrelevant).

We use a table of key/value pairs for storing configuration parameters. Because of a good choice for defaulting, there's no problem and the number of configuration items can be easily extended. Needless to say there's only a small number of parameters here (about 20). We also skipped the PROPERTYTABLE as we store key/value pairs in SUB and don't mind about the data type.

Hope this helps your thinking process

Upvotes: 1

Related Questions