Click here to Skip to main content
15,076,740 members
Home / Discussions / Database
   

Database

 
AnswerRe: Import CSV Problems Pin
RedDk5-Oct-21 8:11
MemberRedDk5-Oct-21 8:11 
GeneralRe: Import CSV Problems Pin
Jörgen Andersson5-Oct-21 23:33
professionalJörgen Andersson5-Oct-21 23:33 
QuestionWHERE NOT EXIST QUERRY Pin
remiki13-Sep-21 5:00
Memberremiki13-Sep-21 5:00 
AnswerRe: WHERE NOT EXIST QUERRY Pin
Richard Deeming13-Sep-21 5:58
mveRichard Deeming13-Sep-21 5:58 
AnswerRe: WHERE NOT EXIST QUERRY Pin
remiki13-Sep-21 6:02
Memberremiki13-Sep-21 6:02 
QuestionDatabase programming Pin
Member 153534049-Sep-21 23:54
MemberMember 153534049-Sep-21 23:54 
AnswerRe: Database programming Pin
Richard MacCutchan10-Sep-21 0:27
mveRichard MacCutchan10-Sep-21 0:27 
QuestionHow to design sql db for storing Name/Value objects with unknown value type (EF Core 5 as ORM)? Pin
Member 146377867-Sep-21 0:43
MemberMember 146377867-Sep-21 0:43 
Hello,

How do you approach typical scenario of custom values (key/value pairs) for an entity? The example would be something like this:

class Person
{
   string Name{get;set;}
   IEnumerable<CustomProperty> CustomProperties {get;set;}
}

class CustomProperty
{
   string Name{get;set;}
   string Value{get;set;}
}


The problem is that Value needs to be of different types so e.g custom property called Color would have a string value of e.g. "Red" while Age would have to be treated as a number (even though it's stored as nvarchar). How would you approach this with a requirement to be able to filter and order over custom properties (multiple at once also with OR/AND) when querying Persons so I'd like to e.g. query over Persons of Age greater than 10 while Value is stored as nvarchar so casting is needed for comparing as numbers instead of strings.

The implementation is based on EF Core 5 and C#.

Possible ways:
1) Building query with casting/converting and adding PropertyType to the CustomProperty entity to decide which type is it. This would store everything in a single table CustomProperties. That's tricky but doable, I guess. The problem would be filtering and ordering with casting everything based on PropertyType. Might not be possible to create every queryable as translatable to TSQL.
2) Creating a separate table per a custom property type so creating tables: StringProperties, IntegerProperties, DecimalProperties which would simply be {string Name, int Number}, {string Name, string Value}, {string Name, DateTime Timestamp} etc. And all these would have a relationship to Person entity, lazy loaded. This would be kinda easy to implement, query and adding new types in the future.
3) The same as 2 but with latest EF Core 5 feature of Table Per Type, meaning there is a base class e.g. CustomProperty with Id and Name and specific classes deriving from base class e.g. IntegerProperty : CustomProperty. EF Core 5 creates the hierarchy in SQL correctly along with FKs. So pretty similar but using the latest feature.
Rant[REPOST] How to design sql db for storing Name/Value objects with unknown value type (EF Core 5 as ORM)? Pin
Richard Deeming7-Sep-21 1:00
mveRichard Deeming7-Sep-21 1:00 
QuestionWhy not to use NoSQL instead of RMDBS? (not Mongo) Pin
Hopeless Idealist14-Aug-21 1:21
MemberHopeless Idealist14-Aug-21 1:21 
AnswerRe: Why not to use NoSQL instead of RMDBS? (not Mongo) Pin
Mycroft Holmes14-Aug-21 12:36
professionalMycroft Holmes14-Aug-21 12:36 
AnswerRe: Why not to use NoSQL instead of RMDBS? (not Mongo) Pin
jschell5-Sep-21 9:07
Memberjschell5-Sep-21 9:07 
QuestionSql query , comparing a table field to a list of values , condition (MS SQL) Pin
zira178929-Jul-21 15:32
Memberzira178929-Jul-21 15:32 
AnswerRe: Sql query , comparing a table field to a list of values , condition (MS SQL) Pin
Richard MacCutchan29-Jul-21 21:15
mveRichard MacCutchan29-Jul-21 21:15 
AnswerRe: Sql query , comparing a table field to a list of values , condition (MS SQL) Pin
Anand RB 20218-Aug-21 18:16
MemberAnand RB 20218-Aug-21 18:16 
GeneralRe: Sql query , comparing a table field to a list of values , condition (MS SQL) Pin
CHill609-Aug-21 23:23
mveCHill609-Aug-21 23:23 
AnswerRe: Sql query , comparing a table field to a list of values , condition (MS SQL) Pin
RedDk10-Aug-21 7:57
MemberRedDk10-Aug-21 7:57 
QuestionLooking for advice on key/value storage options Pin
Chris Maunder16-Jul-21 6:21
cofounderChris Maunder16-Jul-21 6:21 
AnswerRe: Looking for advice on key/value storage options Pin
Mycroft Holmes16-Jul-21 12:23
professionalMycroft Holmes16-Jul-21 12:23 
GeneralRe: Looking for advice on key/value storage options Pin
Chris Maunder16-Jul-21 16:11
cofounderChris Maunder16-Jul-21 16:11 
AnswerRe: Looking for advice on key/value storage options Pin
k505416-Jul-21 14:21
mvek505416-Jul-21 14:21 
GeneralRe: Looking for advice on key/value storage options Pin
Chris Maunder16-Jul-21 16:19
cofounderChris Maunder16-Jul-21 16:19 
AnswerRe: Looking for advice on key/value storage options Pin
Gerry Schmitz9-Aug-21 6:04
mveGerry Schmitz9-Aug-21 6:04 
GeneralRe: Looking for advice on key/value storage options Pin
Chris Maunder9-Aug-21 6:22
cofounderChris Maunder9-Aug-21 6:22 
QuestionOpen Office Database Pin
Bram van Kampen1-Jul-21 14:26
MemberBram van Kampen1-Jul-21 14:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.