Schema ideas
-
In our application the user can define custom "user defined type" called "data context" to store a bunch of named datetime/string/number/list(Guid) such as (for example) -- Contact Information Name Age Address Birthday -- we store that in the DB with some table like that (simplified version) Sets -- a such item, ex: a contact information id Guid idDef Guid -- PK to the type definition table SetValues -- store the values, ex: Contact.Name id Guid idSet Guid -- PK to a record in Sets idDef Guid -- PK to the type definition table Value sql_variant In fact it's a it more complicated (deep) due to list and list with property (2 more tables to store the value of a single user type value) The problem is it's quite cumbersome to do a search for a value for example and we were thinking to store the data instead, in a table like that Sets id Guid string1 nvarchar(max) ... string15 nvarchar(max) datetime1 datetimeoffset(7) .. datetime15 datetimeoffset(7) number1 decimal(8,2) .. number15 decimal(8,2) list1 uniqueidentifier .. list15 uniqueidentifier I'm not sure I explain my problem clearly but.. what I wonder is: is there a clever schema that will allow to store the untyped hierarchical user data, yet makes it easy to join / search, etc... A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station.... _________________________________________________________ My programs never have bugs, they just develop random features.
-
In our application the user can define custom "user defined type" called "data context" to store a bunch of named datetime/string/number/list(Guid) such as (for example) -- Contact Information Name Age Address Birthday -- we store that in the DB with some table like that (simplified version) Sets -- a such item, ex: a contact information id Guid idDef Guid -- PK to the type definition table SetValues -- store the values, ex: Contact.Name id Guid idSet Guid -- PK to a record in Sets idDef Guid -- PK to the type definition table Value sql_variant In fact it's a it more complicated (deep) due to list and list with property (2 more tables to store the value of a single user type value) The problem is it's quite cumbersome to do a search for a value for example and we were thinking to store the data instead, in a table like that Sets id Guid string1 nvarchar(max) ... string15 nvarchar(max) datetime1 datetimeoffset(7) .. datetime15 datetimeoffset(7) number1 decimal(8,2) .. number15 decimal(8,2) list1 uniqueidentifier .. list15 uniqueidentifier I'm not sure I explain my problem clearly but.. what I wonder is: is there a clever schema that will allow to store the untyped hierarchical user data, yet makes it easy to join / search, etc... A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station.... _________________________________________________________ My programs never have bugs, they just develop random features.
Hi, If I understood you correctly, you have already created tables where you can handle dynamic amount of attributes and values. Querying such tables is often a bit tricky if you have several levels etc, but CTE[^] is a helpful tool when handling dynamic hierarchies. On the other hand another possibility could be to use XML data type. This would allow you flatten the relational hierarchy and still be able to use highly dynamic values.
The need to optimize rises from a bad design.My articles[^]
-
Hi, If I understood you correctly, you have already created tables where you can handle dynamic amount of attributes and values. Querying such tables is often a bit tricky if you have several levels etc, but CTE[^] is a helpful tool when handling dynamic hierarchies. On the other hand another possibility could be to use XML data type. This would allow you flatten the relational hierarchy and still be able to use highly dynamic values.
The need to optimize rises from a bad design.My articles[^]
Yeah, XML data type, someone mentioned that... Have to have a look, thanks!! :) I though I knew about CTE already but.. didn't know ou can use them for recursive queries!!! mm... thanks (again!)! A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station.... _________________________________________________________ My programs never have bugs, they just develop random features.
-
Yeah, XML data type, someone mentioned that... Have to have a look, thanks!! :) I though I knew about CTE already but.. didn't know ou can use them for recursive queries!!! mm... thanks (again!)! A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station.... _________________________________________________________ My programs never have bugs, they just develop random features.