October 25, 2011 at 12:17 pm
Damn good question, thanks. I'm also glad it was as long as it was, it took me a few re-reads to realize where you were trying to take this and I had to do some research. Without the length I'd have forgotten about this little footnote and probably gone off on a mental tangent.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 26, 2011 at 1:41 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 26, 2011 at 1:44 am
the question's table design is ugly as
1. person.id is not IDENTITY, so the caller must ensure uniqueness (e.g. sequence)
2. the person.name is not explicitly NOT NULL
3. the NIX_Person_ID_Name UI is rubbish as the PK (just the id field) is unique
4. ditto UI contains all columns so has no justification as covered index (the PK is CI)
- and does NOT ensure that name is unique
5. book.personname has nothing to do with the book, so is presumably intended to be denormalised but is misguided as there relationship is on id (DRY principle)
6. the book.personname is NOT NULL (contrary to #2 above) [yes I realise that was part of the question on validation]
7. if denormalised, should be some trigger to enforce changes to keep accurate
so altogether a nasty example of how not to model relational tables
- I recommend against breeding from this questioner [Steve, sorry for etiquette violation]
October 26, 2011 at 4:01 am
good question!
October 26, 2011 at 5:58 am
Thanks for the question. Lots of reading, but that's OK.
http://brittcluff.blogspot.com/
October 26, 2011 at 6:19 am
dick.baker (10/26/2011)
Removed
Even with all that crap you have nothing against the fk points (which is what the question is ALL about)?
So I guess that means it's a great question.
P.S. If you're having such a crappy day I recommend a punching bag or something similar to flush that out.
October 26, 2011 at 7:16 am
dick.baker (10/26/2011)
the question's table design is ugly
Valid. However, this wasn't a question about table design and instead the table exists solely to make the point about the fact that foreign keys aren't always enforced. For example:
3. the NIX_Person_ID_Name UI is rubbish as the PK (just the id field) is unique
...
5. book.personname has nothing to do with the book, so is presumably intended to be denormalised but is misguided as there relationship is on id (DRY principle)
That nonclustered index existing affects how the script runs. I could probably actually put another question in on that situation.
The personname column was only there to enable a two column foreign key. Could I have thought of something better, yes. Would it have better illustrated the point I was trying to make? I really don't think it would have.
And based on differing opinions about table design I don't think that would make a good question. For example
2. the person.name is not explicitly NOT NULL
Yes, in general this is a good idea but without a complete use case it's impossible to argue that column should never contain a NULL. As the table stands, I agree with you but based on what's here this is clearly a schema that isn't fully developed so more columns may need to be added to the person table and if many entries have NULL names at which point it may be a good idea to allow NULLS. And...
7. if denormalised, should be some trigger to enforce changes to keep accurate
Whether you agree with it or not it is possible to make a valid argument that in a situation where all updates are done through an application and the application can be trusted to not make illegitimate code changes that it's not worth the extra resources and management to use a trigger. I'm not even saying I agree with that but it is a valid argument to the opposite can't be stated as an absolute.
so altogether a nasty example of how not to model relational tables
Yes. And if anyone looks at this question about foreign keys and uses it as a model for designing tables I really feel sorry for them and the future support problems they are guaranteed to have. However, it's my earnest belief that anyone that takes the time to go through these QotDs will know better than that as soon as they read what the question is about.
October 26, 2011 at 7:21 am
@cfradenburg
thanks for your reply, and I apologise for my last line which was out of order
- perhaps Q just hit my funny-bone badly !
October 26, 2011 at 7:25 am
dick.baker (10/26/2011)
@cfradenburgthanks for your reply, and I apologise for my last line which was out of order
- perhaps Q just hit my funny-bone badly !
Hence my punching bag idea :-D.
more constructive in the end...
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply