March 11, 2019 at 3:05 pm
How do you determine table relationship ie 1:1, 1:many, many:many?
March 11, 2019 at 3:20 pm
By looking at the indexes of the columns involved in the join. Could you give us an example of what you're looking at?
If a column participating in a join is declared UNIQUE or PRIMARY KEY, that will be a "one" side. If the column it's joining to in the other table is also unique, then that's a 1:1 relationship. If it's not declared as unique, then it's one-to-many.
Seems an odd question without context, though. Was this an interview question?
March 11, 2019 at 3:45 pm
I understand primary keys. And I understand left outer joins and right outer joins given the business logic. If I'm not given the business logic is there a way to query to determine if it's a left outer join vs right outer join with composite keys when there a millions of records? The query doesn't ever finish.
March 11, 2019 at 4:22 pm
Wait. I'm confused. You tell which are primary keys and foreign keys by looking at the table definitions. Inner joins vs outer joins - that's a join in a query: return matching or all the records from one side or the other of the join.
So what exactly are you trying to determine? Any chance you can post the table definitions of the two tables?
March 11, 2019 at 4:23 pm
Look at the number of unique - distinct - values in the columns you are using. If there are not records in the table that have the same value, then you are almost guaranteed to have a "1" table; if you have duplicate values, then you have a "many" table.
It sounds like you have two tables, and you are joining them using a set of fields. Use those fields to write a query to see if you have duplicate values:SELECT TOP 1 column_1, column_2, COUNT(*)
FROM table_name
GROUP BY column_1, column_2
HAVING COUNT(*) > 1
If the query returns a row, you have at least one set of duplicate values, so that is a "many" table. If it returns no rows, that is probably a "1" table. Do that with both tables in your query, and you should be able to determine your relationships.
March 11, 2019 at 5:17 pm
Thanks, that's what I've been doing. It's a huge database, no keys.
March 12, 2019 at 5:56 am
tinafmills - Monday, March 11, 2019 5:17 PMThanks, that's what I've been doing. It's a huge database, no keys.
Yikes, no keys... You're on your own really. That means it's completely undefined. Wow! The optimizer is not your buddy in this case since it has no tools to work with. Performance there is going to suck bad. Strong recommendation, start getting keys in place.
Quick answer to the original question:
1-1: A unique key on the 1 side and a unique key on the other 1 side with a foreign key between them.
1-Many: A unique key on the 1 side and a foreign key between them.
Many-Many: This involves what we call a many-to-many, or interim, table, so three tables we'll call A, B, and I for interim. A unique key on the A table and a foreign key to the I table. A unique key on the B table and a foreign key on the I table. Then, a unique key on the I table that is a compound key of the A & B keys.
Good luck in your cleanup efforts. You'll just have to explore the data carefully. Problem is, you're very likely to have dirty data since there's been nothing to enforce the integrity of the data. This means that, just on a simple 1-Many, you're going to see non-unique values on the 1 side and values on the Many side that don't match any values on the 1 side. It's going to be a right mess. As I said, good luck.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply