Hi guys,
I think this topic might have many possible answers and most of these must be already somewhere in the forum. Anyway, my apologies if that is the case.
I have a 'documents' table (for instance) where any doc can be related to any doc(s). So I need a many to many within the same table.
I have tryed many solutions, the best I found was to have ID column twice in documents table and then I can link them both as 1 to many to the relations table, which also haves ID1 and ID2 columns. That way I also ensure ref.integrity. but when I query this join I have to perform inline views or whatever else to have ID1 and ID2 both as a single column....specially thinking queries for documents are already way too big as to add this extra level of complexity (mines arent actually documents, in fact this table represents a generic "element" in my app and can be joined to many other things depending on how the item is defined and therefore, behaves - so it is actually an 'elements' table... being an element a very generic mofo)
I couldnt find a simple approach. Any ideas? Thanks!!!
Regards,
Mauro
Typically, a 'self-JOIN' can be used in situations such as this. Element may have one or more sub-elements, which in their own right are elements.
A good example of how to accomplish this is examining the Employees table in the Northwind database. Each employee has a unique EmployeeID, and most employees also have a supervisor -or ReportsTo. The Supervisor is in turn an Employee.
To return a list of Employees and their supervisors, simply JOIN the Employees table to itself, such as (this works in SQL 2000 or 2005):
USE Northwind
GO
SELECT
EmployeeName = ( e.FirstName + ' ' + e.LastName ),
Supervisor = ( s.FirstName + ' ' + s.LastName )
FROM Employees e
JOIN Employees s
ON e.ReportsTo = s.EmployeeID
However, this does not allow additional recursion, i.e., retrieving the Supervisor's supervisor.
In SQL 2005, new functionality was included to allow recursion. Using a [Common Table Expression] or cte, you can easily recurse as many levels as necessary.
USE Northwind
GO
WITH OrgChart (EmployeeID, ReportsTo, Employee, Level)
AS
( SELECT
e.EmployeeID,
e.ReportsTo,
Employee = e.FirstName + ' ' + e.LastName,
Level = 0
FROM Employees AS e
WHERE ReportsTo IS NULL
UNION ALL
SELECT
e.EmployeeID,
e.ReportsTo,
Employee = e.FirstName + ' ' + e.LastName,
Level + 1
FROM Employees e
JOIN OrgChart o
ON e.ReportsTo = o.EmployeeID
)
SELECT
EmployeeID,
ReportsTo,
Employee,
Level
FROM OrgChart
And then there are also excellent xml possibilites.
|||
Hi Arnie, sorry for the delay but I did not get an automatic notification.... I just came here 'for if' and saw your answer.
About it, that's ok, I love CTE's but my need is a bit different.
- It is not a hierarchical relationship, not recursive, and many elements can be related to many.
In your example, in the very row there's the related (parent) id and only 1.
A doc can have n related docs. For sure I will need an extra table. I just want to know if there's an easy and handy way out or standard solution. Otherwise, as i said before, I already have a workaround, but I was looking for something simpler than my actual schema.
Anyway, thanks a lot for your comment.
Mauro
|||Perhaps you need a 'CROSS-JOIN' between two instances of the same table.|||Well, I took a look but even when it is possible (conceptually, what I need is a cross join - but not in practice), doing it that way would be a nightmare. Thanks anyway!
I'm beggining to think it was a good idea to ask this about 1 month before tahn what I actually need it! It's the good think of building db's in a notepad while i'm on the train :P
I appreciate you affort guys.... c'mon there must be a solution!
Mauro
|||There is no real easy answer for this. From what I can gather from your description, I think you need to implement what is known as a graph. There are some ways to work with these, and it is usually quite messy. The simplest implementation is just a relationship table:
Object --table
========
ObjectId
<OtherColumns>
ObjectRelationship
===============
ParentObjectId
ChildObjectId
RelationshipType
This would limit you to two members, and would "order" the relationship. You could also do something like
ObjectRelationship
===============
ObjectRelationshipId
RelationshipType
ObjectRelationshipMember
=====================
ObjectRelationshipId
ObjectId
This would allow unlimited members.
You can use the same sort of "breadth-first" CTE to query this data, one level from the node you are starting from at a time. If you are going to be doing a lot of querying, you might want to look at the following book:
Joe Celko's Trees and Hierarchies in SQL for Smarties
http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202/ref=pd_bbs_sr_2/102-7229005-3497703?ie=UTF8&s=books&qid=1174958839&sr=8-2
|||
ok, for what I have seen and could quote "there's no real easy answer" I must conclude I will stick to my original approach. And I will tell you more:
In a previous project (actually, it was the old asp 3.0 version of this app) I had to do this and this is what i did:
1. Documents' table
2. relations table
doc_id1 (related to documents)
doc_id2 (no explicit relation)
3.A view selecting all id1, with union to select id2, with distinct.
4.A trigger to delete cascade from documents on id2 (to ensure ref.int.)
With all that, I solved the problem in a fairly simple and easy to understand way.
I must say I was hopping there was an 'official' solution, but i see many to many is some way not a common issue (well it is and has been for me before) so it's a 'do it as u can'
Unless any of you disagree, I will leave this unanswered and get my hands on it.... I will just use the old method I used some years ago. Not a big deal anyway.
Thanks a lot to all of you who helped me here.
Mauro
Mauro_Net wrote:
ok, for what I have seen and could quote "there's no real easy answer" I must conclude I will stick to my original approach. And I will tell you more:
In a previous project (actually, it was the old asp 3.0 version of this app) I had to do this and this is what i did:
1. Documents' table
2. relations table
doc_id1 (related to documents)
doc_id2 (no explicit relation)
3.A view selecting all id1, with union to select id2, with distinct.
4.A trigger to delete cascade from documents on id2 (to ensure ref.int.)With all that, I solved the problem in a fairly simple and easy to understand way.
I must say I was hopping there was an 'official' solution, but i see many to many is some way not a common issue (well it is and has been for me before) so it's a 'do it as u can'Unless any of you disagree, I will leave this unanswered and get my hands on it.... I will just use the old method I used some years ago. Not a big deal anyway.
Thanks a lot to all of you who helped me here.
Mauro
The solution I've been using for years with these types of problems is nearly identical to what you have listed here. So much so that I would be willing to call it a "standard" solution. Let's take a document bibliography, for example. Let's say you're building a database that drives an app which collects white papers for a conference. It is quite possible that the documents you collect will reference other documents you are collecting.
So, for starters, you'd have a Documents table:
(DocumentID, Title, ....) and so on.
Next, you would have a reference table to provide a many-to-many relationship:
(DocumentID, ReferencedDocumentID)
Retrieving a document's bibliography, at this point, is a simple exercise (as I'm sure you know). And in this model, any document can be simultaneously a parent document to other documents, as well as a child document to other documents. In my opinion, this is about as close to a "standard" solution as you can get with this type of problem if normalization is important to you. I've seen denormalized answers to this type of problem, but I believe they are very sloppy and difficult to work with.
Also, there's no reason I can think of (albeit I'm tired right now) to not include an explicit relationship on "ReferencedDocumentID". I believe you can still declare this as a migrated foreign key from the Documents table. Just make sure that you do NOT turn on cascade deletes for that FK relationship!!! You definitely don't want to delete the parent document record just because that document is no longer used as a reference by any other document!
I forgot to mention when I posted this earlier: Making a composite primary key in this reference table (combination of the two foreign keys) will also give you the added benefit of guaranteeing no duplicate records can be inserted.
No comments:
Post a Comment