Identifying a one to many relation

[tomas79]

If i write a query based on a one to many relationship, how do I know from looking at the output that its based on a 1 to many relationship? Would appreciate your feedback.

For Example:

SalesHeader Table

SalesID CustAccount Product
1 123 21801
2 456 50001
3 789 22550

SalesLine Table
SalesID LineNumber CustAccount Product Amount Qty
1 1 123 21801 100 2
1 2 123 21801 200 4
2 1 456 50001 100 2
3 1 789 22550 150 1
3 2 789 22550 100 3

if I write a query to join these 2 table, I would have the following output:

Query
select
sh.salesid,
sh.custaccount,
sl.salesid,
sl.linenumber
from salesheader sh
join salesline sl
on sh.salesid = sl.salesid

sh.salesid sh.custaccount sl.salesid sl.linenumber
1 123 1 1
1 123 1 2
2 456 2 1
3 789 3 1
3 789 3 2

How from looking at this output can i see that it is based on a one to many relationship?

Do I need to look at what fields I used to join the tables with.

I have used a simple example so would really appreciate your comments.

THe same applies to:
1 to 1
many to many

Are there tools/apps that can be used to identify the relationshio types?

Regards,
Tomas

 

[r937]

you really cannot definitively tell anything from looking at data

if you have access to the database in order to run queries, then you can probably run queries against the INFORMATION_SCHEMA database as well

that can easily tell you what the relationships are, by identifying the foreign keys

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ChrisHunt]

Frankly, if two tables in a database are related, it's almost always a many-to-one relationship. You can't have a many-to-many realtionship between tables (you have to split it out into two many-to-ones); and in 20 years of working with and designing databases, I've only ever seen one one-to-one (and that was for disk space reasons in the olden days when disk was expensive).

The way you would detect what's going on is to examine your joins against the schema. If you're joining a foreign key on table A to the primary key of table B, it's a many-to-one, with A as the many and B as the one.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SQLSister]

Well frankly this is something you should know from the content of the data before you look at any of the records. For instance orders to order details is clearly a one to many relationship, an order can have many individual line items.

If you want to know if a child table has multiple records for any FK, then this will work:

Select count(*), FK_ID from childtable group by FK_ID having count(*)>1

If any records are returned, you have a one to many relationship or a many to many relationship.

Generally a many to many relationship will consist of a table that contains two FKS related to two differnt tables.
Again thsi is usally obvious if you understand what the table is for. Students and courses are related in a many to many relationship, one course can have many students, one student can take many courses. Usually these relationships are shown in a join table that countains the PK from students and the PK from courses and nothing else.

One-to-one relationships are best enforced by using the parent PK as the PK in the child table. So if USERID is the PK of both tables (and the FK in the child tables as well), likely it is a 1-1 relationship. The query above will return zero results of course.

"NOTHING is more important in a database than integrity." ESquared