|
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
if you can't read my code, try converting it here[^]
|
|
|
|
|
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 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<Property> claimEvents =
new ObservableCollection<ClaimEvent>(_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<ClaimEvent> 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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
All,
I am writing a library that provides communication like .NET remoting or SOAP but is far more flexible in terms of supported languages and platforms. Part of the program involves transmitting exceptions across the boundary between the connected programs. Because it runs on several languages, there isn't really a standard set of exceptions. What exceptions do you think are important? I am curious what the general community thinks.
|
|
|
|
|
All of them, that's why they're called exceptions. In C++ you can throw objects that do not inherit from Exception , how would the other side react to that if it were implemented in C#?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Yes, all exceptions are already passed back to the client but the exception names are not standard across different languages. I am trying to come up with a table that will be translated to language-native exceptions.
|
|
|
|
|
johndw94 wrote: the exception names are not standard across different languages
Can you clarify "different languages"?
Different in .NET? The classname of an IndexOutOfRangeException[^] is the same for each .NET language.
Different across all computer-languages, thus including the TIndexOutOfRangeException[^]? In that case, limit yourself to the exceptions that have already been reported.
Different across human languages? Try unlocalize.com[^]
Logging of exception-text in the English language? Set the culture to English when you ToString. You should be able to build that table for .NET by enumerating all the classes that inherit from Exception , using Reflection.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Current targets are:
.NET (C# and VB.net mainly)
C++ (boost::exception)
Python
Java
MATLAB
Octave
SciLab
Perl
ActionScript
Possible targets are
PHP
TCL
None of them have common exception types.
|
|
|
|
|
The usual way round this is to have your library provide core functionality, and its own set of internal exceptions. You then add a wrapper for each language which maps method/function calls and internal library data to the types required by the language.
[edit]
Alternatively, forget about exceptions and just use return codes to indicate success or failure of client requests. XML offers many possible ways of achieving this.
[/edit]
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
My question was WHICH exceptions to wrap. I already implemented the rest of these comments a long time ago...
|
|
|
|
|
All of them, obviously. There is little point in writing a library that provides communication like .NET remoting or SOAP but is far more flexible in terms of supported languages and platforms, unless it can handle all types, which is, after all, what you claim to be doing.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Yes, all exceptions are already passed back. They just don't have a common name. Passing the name of a C# exception to a Python program won't help much .There are a core set of exceptions that should be handled in a neutral manner. The question is, what are those core exceptions?
|
|
|
|
|
axpnumtheory wrote: The question is, what are those core exceptions? The only way such a question could be answered is to list every exception from every language that you plan to support and exclude any that are not common. I have no idea whether such a list has already been created by anyone else.
One of these days I'm going to think of a really clever signature.
|
|
|
|