June 9, 2010 at 8:16 am
Hello forum
I would like to know what is in a one to one relationship that isn't obtained by merging it into two seprate tables ?
What is the application of having a one-to-one relationships between two tables?
Thanks and best regards
June 9, 2010 at 8:37 am
The reason I used to do this in the past usually broke down into two scenarios. Scenairo #1, way too many columns to make up a single table, so I'd break them into two. Although, to be honest, if you're in a situation where you have that many columns there are other issues. Seconary #2, there were lots of columns that just weren't going to be filled out regularly, so I peeled them off to another table so that it was easier to keep & maintain the table that had the really important data. This one I kind of regret, but it did make some coding easier in some situations.
In general, I would tend towards keeping all the associated data together in a single table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2010 at 8:38 am
Although this sounds like homework I'll answer anyway..
Your first question is about what ISN'T obtained, and the answer is nothing I can think of. However that begs the question why split them up.. One of the most common reasons is that the record is largely static and there are a few volatile (or VERY volatile) columns and it takes less time to write a row that is very narrow (column and type wise). However with that said I very rarely use this method without thinking long and hard about it. You also need to make sure to put processes in place to make sure that there is ALWAYS a record in place in the secondary table because what tends to happen is the primary's record gets written and the secondary might not, and because when developers know that there is supposed to be a 1:1 they like to do a simple inner join and then start losing records because the secondary record isn't there.. I've seen it many times, one of the most common resolutions is to handle ALL creates of records in a sproc and in a transaction, this makes sure that your process will always create both sides of the record AND that both will exist or not at all.
And I think I might have answered your second question already.
CEWII
June 9, 2010 at 3:01 pm
Relation Ship? Does that boat stop at hedonism in Jamaica...lol
June 9, 2010 at 3:14 pm
Nice point on the INNER JOIN and the issues it causes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2010 at 5:10 pm
I mostly do this for Grant's reasons, but nice catch Elliot on missing rows in the second table.
June 9, 2010 at 5:14 pm
Put one more feather in that cap. I do it for the same reasons as Grant mentioned. As Elliot said, proceed cautiously and make sure it is well thought out in the design and code.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply