Utility Field in Database
-
I have three tables, ClaimEvent, Event, and EventScope. The actual ClaimEvent can be relevant to an entire claim, or a single property within a claim. Every property has a claimId. As such, I have designed my tables like this: Event id | Int Identity AI name | varchar scopeId | int EventScope id | Int Identity AI name | varchar ClaimEvent id | Int Identity AI eventId | Int FK to Event targetId | Int date | DateTime My idea is to use the targetId field in the ClaimEvent table to refer either to the Claim ID (if the event is relevant to the entire claim), or the Property ID (if the event is relevant only to the current property). If I want all events relevant to the selected claim, including properties within that claim, I can just match the targetId field with the id field in the property table when the scope is "property wide". Alternatively, I would have to design the table like this: ClaimEvent id | Int Identity AI eventId | Int claimId | Int propertyId | Int? date | DateTime With the propertyId field frequently being null. However, I would be able to force FK constraints to the Claim and Property tables, which I can't do otherwise. The data is all going to be entered through a data entry form, with drop downs and very little possibility of FK violations, but it is always possible. What do you guys think? I am always a fan of minimizing fields that will often be null, especially when I can repurpose another field, but it feels a little hackish. Cheers, --EA
-
I have three tables, ClaimEvent, Event, and EventScope. The actual ClaimEvent can be relevant to an entire claim, or a single property within a claim. Every property has a claimId. As such, I have designed my tables like this: Event id | Int Identity AI name | varchar scopeId | int EventScope id | Int Identity AI name | varchar ClaimEvent id | Int Identity AI eventId | Int FK to Event targetId | Int date | DateTime My idea is to use the targetId field in the ClaimEvent table to refer either to the Claim ID (if the event is relevant to the entire claim), or the Property ID (if the event is relevant only to the current property). If I want all events relevant to the selected claim, including properties within that claim, I can just match the targetId field with the id field in the property table when the scope is "property wide". Alternatively, I would have to design the table like this: ClaimEvent id | Int Identity AI eventId | Int claimId | Int propertyId | Int? date | DateTime With the propertyId field frequently being null. However, I would be able to force FK constraints to the Claim and Property tables, which I can't do otherwise. The data is all going to be entered through a data entry form, with drop downs and very little possibility of FK violations, but it is always possible. What do you guys think? I am always a fan of minimizing fields that will often be null, especially when I can repurpose another field, but it feels a little hackish. Cheers, --EA
eddieangel wrote:
What do you guys think? I am always a fan of minimizing fields that will often be null,
Then move the propertyId to it's own table, and only insert a record when it's not null. Link back using the ClaimEventId to which the propertyId belongs.
eddieangel wrote:
especially when I can repurpose another field, but it feels a little hackish.
..repurposing a field sounds hackish indeed :)
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
I have three tables, ClaimEvent, Event, and EventScope. The actual ClaimEvent can be relevant to an entire claim, or a single property within a claim. Every property has a claimId. As such, I have designed my tables like this: Event id | Int Identity AI name | varchar scopeId | int EventScope id | Int Identity AI name | varchar ClaimEvent id | Int Identity AI eventId | Int FK to Event targetId | Int date | DateTime My idea is to use the targetId field in the ClaimEvent table to refer either to the Claim ID (if the event is relevant to the entire claim), or the Property ID (if the event is relevant only to the current property). If I want all events relevant to the selected claim, including properties within that claim, I can just match the targetId field with the id field in the property table when the scope is "property wide". Alternatively, I would have to design the table like this: ClaimEvent id | Int Identity AI eventId | Int claimId | Int propertyId | Int? date | DateTime With the propertyId field frequently being null. However, I would be able to force FK constraints to the Claim and Property tables, which I can't do otherwise. The data is all going to be entered through a data entry form, with drop downs and very little possibility of FK violations, but it is always possible. What do you guys think? I am always a fan of minimizing fields that will often be null, especially when I can repurpose another field, but it feels a little hackish. Cheers, --EA
I like the idea of having the two distinct fields, for claim and property. As you said, you can apply constraints cleanly to the Claim and property tables which is a definite plus, but also the null value property does have a meaning - it refers to all the properties of the claim and not just the claim itself.
Panic, Chaos, Destruction. My work here is done. Drink. Get drunk. Fall over - P O'H OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett
-
I have three tables, ClaimEvent, Event, and EventScope. The actual ClaimEvent can be relevant to an entire claim, or a single property within a claim. Every property has a claimId. As such, I have designed my tables like this: Event id | Int Identity AI name | varchar scopeId | int EventScope id | Int Identity AI name | varchar ClaimEvent id | Int Identity AI eventId | Int FK to Event targetId | Int date | DateTime My idea is to use the targetId field in the ClaimEvent table to refer either to the Claim ID (if the event is relevant to the entire claim), or the Property ID (if the event is relevant only to the current property). If I want all events relevant to the selected claim, including properties within that claim, I can just match the targetId field with the id field in the property table when the scope is "property wide". Alternatively, I would have to design the table like this: ClaimEvent id | Int Identity AI eventId | Int claimId | Int propertyId | Int? date | DateTime With the propertyId field frequently being null. However, I would be able to force FK constraints to the Claim and Property tables, which I can't do otherwise. The data is all going to be entered through a data entry form, with drop downs and very little possibility of FK violations, but it is always possible. What do you guys think? I am always a fan of minimizing fields that will often be null, especially when I can repurpose another field, but it feels a little hackish. Cheers, --EA
I prefer the second approach. There's nothing wrong with a field often being null if it actually is often 'null', i.e. no specific property, which is true in this case. A lookup which goes into one of two tables is weird and should only be done if there's a very good reason to. I have had to do it in a real application so I won't say 'never', but that was a bit uncomfortable. I don't see that this is a case where that's true. The only other sane way would be if claims and properties made a tree structure in one table, and you could just use an ID into that table. But I doubt that that is the case, because claims and properties are different things.
-
I prefer the second approach. There's nothing wrong with a field often being null if it actually is often 'null', i.e. no specific property, which is true in this case. A lookup which goes into one of two tables is weird and should only be done if there's a very good reason to. I have had to do it in a real application so I won't say 'never', but that was a bit uncomfortable. I don't see that this is a case where that's true. The only other sane way would be if claims and properties made a tree structure in one table, and you could just use an ID into that table. But I doubt that that is the case, because claims and properties are different things.
There is a parent - child relationship between claim and property. Every property does have a claimId already. So in theory when looking to get all claim information (with properties) I could write:
SELECT * FROM ClaimEvent
WHERE targetId = @ClaimId
OR targetId in (SELECT id FROM Property WHERE claimId = @ClaimId)That would get all of the event dates that I need, I just don't like to have to use the IN query if I could avoid it. In actuallity this is going to be accessed through LinqToEF so it is going look more like:
var propertyIds = _db.Properties.Where(p => p.claimId == _claim.id).Select(p => p.id);
ObservableCollection claimEvents =
new ObservableCollection(_db.ClaimEvents
.Where(c => propertyIds
.Contains(p.claimId) || c.claimId == _claim.id));If the table had claimId and propertyId it would look like this:
ObservableCollection claims = _db.ClaimEvents.Where(c => c.claimId == _claimId);
I don't really think I like the additional overhead of the subquery (In this case the addition of var propertyIds due to L2EF limitations). Anyways, thanks everyone for your input, I am going to go ahead and add the propertyId field to the ClaimEvent table. I suppose I could go so far as to have ClaimEvent for Claim specific events and PropertyEvent for property specific events, and I might end up doing that, though it irks me to add yet another table.
-
There is a parent - child relationship between claim and property. Every property does have a claimId already. So in theory when looking to get all claim information (with properties) I could write:
SELECT * FROM ClaimEvent
WHERE targetId = @ClaimId
OR targetId in (SELECT id FROM Property WHERE claimId = @ClaimId)That would get all of the event dates that I need, I just don't like to have to use the IN query if I could avoid it. In actuallity this is going to be accessed through LinqToEF so it is going look more like:
var propertyIds = _db.Properties.Where(p => p.claimId == _claim.id).Select(p => p.id);
ObservableCollection claimEvents =
new ObservableCollection(_db.ClaimEvents
.Where(c => propertyIds
.Contains(p.claimId) || c.claimId == _claim.id));If the table had claimId and propertyId it would look like this:
ObservableCollection claims = _db.ClaimEvents.Where(c => c.claimId == _claimId);
I don't really think I like the additional overhead of the subquery (In this case the addition of var propertyIds due to L2EF limitations). Anyways, thanks everyone for your input, I am going to go ahead and add the propertyId field to the ClaimEvent table. I suppose I could go so far as to have ClaimEvent for Claim specific events and PropertyEvent for property specific events, and I might end up doing that, though it irks me to add yet another table.
eddieangel wrote:
In actuallity this is going to be accessed through LinqToEF so it is going look more like:...
You don't design a database based on how many characters a query takes so what is your point?
eddieangel wrote:
I don't really think I like the additional overhead of the subquery
Because the tables will have hundreds of millions of rows? Or because you will be doing these queries hundreds of times a second? In either case you then design the database based on performance/volume needs - specifically. And if not then there is no measurable much less significant overhead so it doesn't matter.
-
eddieangel wrote:
In actuallity this is going to be accessed through LinqToEF so it is going look more like:...
You don't design a database based on how many characters a query takes so what is your point?
eddieangel wrote:
I don't really think I like the additional overhead of the subquery
Because the tables will have hundreds of millions of rows? Or because you will be doing these queries hundreds of times a second? In either case you then design the database based on performance/volume needs - specifically. And if not then there is no measurable much less significant overhead so it doesn't matter.
I think I might have only gotten a partial view of your post because while I see the criticism I don't see anything helpful. Was it only a partial post? If not, thank you for your input. --EA
-
I think I might have only gotten a partial view of your post because while I see the criticism I don't see anything helpful. Was it only a partial post? If not, thank you for your input. --EA
-
eddieangel wrote:
I don't see anything helpful.
In either case you then design the database based on performance/volume needs - specifically. And if not then there is no measurable much less significant overhead so it doesn't matter.
Oh there it is, buried beneath condescension. Thank you for your input.
-
Oh there it is, buried beneath condescension. Thank you for your input.
-
eddieangel wrote:
Oh there it is
Yep. Based on decades of experience with database including profiling them and designing them along with working on a number of ones with large volumes.
Don't get me wrong, I don't doubt the validity of the information you provided or your decades of information. Rather, I just wanted to check the size of the boot it was delivered on. If you can't find a way to be nice when someone asks a question, after decades of working in the field, maybe you should consider a better way to spend the next couple of decades. I might have only been at this for ten years but I have figured out that someone asking a question probably just wants an answer to that question, they don't need to pay the price of a brow beating or sarcasm because of my experiences. And that piece of knowledge comes from decades of being a decent human being. Consider it a fair trade for your piece of information and don't feel obligated to answer any of my questions going forward. That is my two cents, feel free to ignore it as I am sure I am just misinterpreting your tone on account of the internet being in between us.
-
Don't get me wrong, I don't doubt the validity of the information you provided or your decades of information. Rather, I just wanted to check the size of the boot it was delivered on. If you can't find a way to be nice when someone asks a question, after decades of working in the field, maybe you should consider a better way to spend the next couple of decades. I might have only been at this for ten years but I have figured out that someone asking a question probably just wants an answer to that question, they don't need to pay the price of a brow beating or sarcasm because of my experiences. And that piece of knowledge comes from decades of being a decent human being. Consider it a fair trade for your piece of information and don't feel obligated to answer any of my questions going forward. That is my two cents, feel free to ignore it as I am sure I am just misinterpreting your tone on account of the internet being in between us.
eddieangel wrote:
Don't get me wrong, I don't doubt the validity of the information you provided or your decades of information. Rather, I just wanted to check the size of the boot it was delivered on. If you can't find a way to be nice when someone asks a question, after decades of working in the field, maybe you should consider a better way to spend the next couple of decades.
Or perhaps you imposed your own emotional interpretation onto what I said?
eddieangel wrote:
I might have only been at this for ten years but I have figured out that someone asking a question probably just wants an answer to that question
Having probably posted upwards of 100,000 replies on forums my experience is that people do often want the answer that they expect but they often (to some degree) do not know that alternatives exist.
eddieangel wrote:
And that piece of knowledge comes from decades of being a decent human being
Mine comes from answering many, many questions.
-
eddieangel wrote:
Don't get me wrong, I don't doubt the validity of the information you provided or your decades of information. Rather, I just wanted to check the size of the boot it was delivered on. If you can't find a way to be nice when someone asks a question, after decades of working in the field, maybe you should consider a better way to spend the next couple of decades.
Or perhaps you imposed your own emotional interpretation onto what I said?
eddieangel wrote:
I might have only been at this for ten years but I have figured out that someone asking a question probably just wants an answer to that question
Having probably posted upwards of 100,000 replies on forums my experience is that people do often want the answer that they expect but they often (to some degree) do not know that alternatives exist.
eddieangel wrote:
And that piece of knowledge comes from decades of being a decent human being
Mine comes from answering many, many questions.
No emotional response, just basic manners. Be nice to people if you want them to be nice to you kind of stuff. I see you have a stellar reputation here and I am sure no one else has any problems. That is why I simply invited you to ignore my posts outright from here on out, there are 99,999 more posts that are less likely to provoke. Again, I have no problem with the quality of the answer of your question, I am sure you are very knowledegable in your field. Best of luck in your next 100,000 replies.
-
No emotional response, just basic manners. Be nice to people if you want them to be nice to you kind of stuff. I see you have a stellar reputation here and I am sure no one else has any problems. That is why I simply invited you to ignore my posts outright from here on out, there are 99,999 more posts that are less likely to provoke. Again, I have no problem with the quality of the answer of your question, I am sure you are very knowledegable in your field. Best of luck in your next 100,000 replies.
eddieangel wrote:
Be nice to people if you want them to be nice to you kind of stuff
My response was not intended to be nice nor not nice. Your assumption is that my response was not nice. That it in some way was intended to be not nice. That is you inferring something that isn't there.
eddieangel wrote:
nd I am sure no one else has any problems.
Other people have had various types of problems with my responses. Most are with the correctness of the technical advice that I gave.
-
eddieangel wrote:
Be nice to people if you want them to be nice to you kind of stuff
My response was not intended to be nice nor not nice. Your assumption is that my response was not nice. That it in some way was intended to be not nice. That is you inferring something that isn't there.
eddieangel wrote:
nd I am sure no one else has any problems.
Other people have had various types of problems with my responses. Most are with the correctness of the technical advice that I gave.
Well, thank you again for the additional correction. Good luck to you.