CODEWITHSUNDEEP
databasedatabase-designjoinrelational-database
mgPePe
mgPePe

Reputation: 5907

How do I organize related tables in a database

I have the following situation:

table User (id, name)
table Belonging (id, name, color)
table UserBelonging (id, user_id, belonging_id)

Now, UserBelonging is my join table that connects User and his Belonging.

The problem arises when I want to pull out all belongings of a certain User, that are red.

I see two ways to do that:

  1. Get all UserBelonging for a certain user, then loop through them and check which are red
  2. Put an extra column like that: UserBelonging (id, user_id, belonging_id, color) and then query in the same table.

Both methods seem ugly to me. Is there anyone with experience in database tables that could tell me which way is better and why or possibly give me a better solution?

thanks

Upvotes: 0

Views: 81

Answers (4)

tim franklin
tim franklin

Reputation: 118

It's generally best not to use loops in databases. In SQL Server I would do it like this (but I don't know if it's applicable to your DB)

    Select User.Name, Belonging.name, Belonging.color from User inner join UserBelonging 
    on User.id =     UserBelonging.user_id inner join Belonging 
    on UserBelonging.belonging_id = Belonging.id 
    where Belonging.color = 'red' and User.name = 'put the name here'

regards

Tim

Upvotes: 0

hatchet - done with SOverflow
hatchet - done with SOverflow

Reputation: 16257

SELECT B.* FROM User U
INNER JOIN UserBelonging UB on UB.UserId=U.Id
INNER JOIN Belonging B on B.Id = UB.BelongingId
Where B.Color = 'red' AND U.Name='Sam'

I think a join like this will do what you want.

Upvotes: 1

Sleeperson
Sleeperson

Reputation: 612

You can easily use 1 JOIN and A WHERE statement with your color condition and user_id. Joins are one of the most common operations when discussing about relational-databases. Check W3schools for some basic examples.

Upvotes: 0

Matt Ball
Matt Ball

Reputation: 359966

The join table should only contain user_id and belonging_id, with either a composite ID of (user_id, belonging_id) or an autoincrement id column.

That rules out #2.

Now, you could use strategy #1, or you could filter out non-red belongings in the select statement.

SELECT b.id, b.name, b.color
FROM UserBelonging ub
LEFT JOIN Belonging b
ON ub.belonging_id = b.id
WHERE ub.user_id = 123456789
AND b.color = 'red'

Upvotes: 1

Related Questions