Any database designers out there per chance?

[Dirty Hippie]

I need to find the best way to solve a complex relationship between two entities. If anyone thinks they can help I'll post or PM the problem.

I'll be forever in your debt.

[BlueEyedSon]

1. Ok. Shoot.

[BlueEyedSon]

16. Here ya go....

REL_TAB
------------------------
REL_ID unique key
REL_TYPE required
REL_NAME optional
(other relationship attribs)


HOSP_REL_TAB
------------------------
HOSP_REL_ID unique key
REL_ID foreign key
HOSP_ID foreign key


HOSP_TAB
------------------------
HOSP_ID unique key
(other hospital attribs)


select * from HOSP_TAB
where REL_ID in
(select REL_ID
from HOSP_REL_ID
where HOSP_ID = $1)


Notes:

you can screen on REL_TYPE in the subquery if you need to (otherwise you get all regardless of type)

you may want to exclude the hospital in question in the outer query with a not equal predicate

the HOSP_REL_TAB should have a constraint that the combo of REL_ID and HOSP_ID must be unique

you can reproduce your many-to-many table by a self join on HOSP_REL_TAB.REL_ID

The *secret* is to code a table relating hospitals to their relationship, not directly to each other.

[sangh0]

20. Ding! We have a winner

That's how it's done.

[BlueEyedSon]

21. more info on my other post

http://www.democraticunderground.com/discuss/duboard.php?az=show_mesg&forum=105&topic_id=1970071&mesg_id=1970681

[molly]

2. You didn't say what the platforms are?

[sui generis]

3. fire away -

what database platform are you using?

[KurtNYC]

4. Sounds like a many-to-many

[LynzM]

5. Depends what DB you're working in

But go ahead and shoot me a PM (though there are much more qualified people around...) :)

[Dirty Hippie]

6. OK

The platform as not yet been determined. Were talking the modeling phase.

Here is the story. We have a table of hospitals. We need to model the fact that hospitals form partnerships with each other. They form partnerships of different types, that is, they may share equipment, or they may share staff, or they may share supplies. Of course, more relationship types may be added in the future.

We have an entity (table) for hospitals I suggested a table for relationship types with a linking table in between. That is fairly straightforward.

The sticky part is the linking table. If hospital A has a relationship with hospital B to share equipment, the ID for A may be in the first field, the ID for B in the second OR they may be reversed.

I was able to write SQL code against a sample database to retrieve all the hospitals a certain hospital has relationship with by using the union operator between two select queries, each checking each field but it seemed kludgey.

I hope I've explained the problem well.

[bobbobbins]

7. if the IDs are numeric

perhaps you could just code it so the smaller ID is always in the first column and the larger always in the second.

[bobbobbins]

8. nevermind, that wouldn't solve the problem...

[Mister K]

9. Two tables will suffice

Hospital Table
--Hospital ID
--Hospital Specific Data Here


Hospital Sharing Table
--Hospital ID 1
--Hospital ID 2
--Type of Sharing (Equipment, people, etc)

Nice homework problem!

[Dirty Hippie]

10. Here is some clarification:

Again, the linking table has 3 fields, Hospital1, Hospital2 and RelationshipID.

Here is some sample data:

Hospital1    Hospital2    RelationshipID
A            B            3
A            C            1
D            A            2

So, if I want to find all of the hospitals that have a relationship with hospital A I have to specify A can be in field Hospital1 OR Hospital 2 and the hospital I want to list as haveing a relationship with A can be in either field.

Like I said, I can UNION two SELECTS, I just want to know if I'm overlooking simpler solution and if the underlying design approach is the best.

[Mister K]

11. Are you looking for the SQL to select the data?

[bobbobbins]

12. can't you just do

select * from whatever where (hospital1='$x' and hospital2='$y') or (hospital1='$y' and hospital2='$x');

[Dirty Hippie]

13. Maybe I'm confused but

If you look at my post 10

If I want a list of all hospitals that have a relatiuonship with A I want the results to be:

B      3
C      1
D      2

So you see, the hospital in the result can be from either field.

[Dirty Hippie]

14. BTW

in case anyone is wondering, I'm not cheating on homework. I'm actually a teacher.

[bobbobbins]

15. well then wouldn't this work...

select * from whatever where hospital1='A' or hospital2='A';

[BlueEyedSon]

17. delete

[DS1]

18. What if you were to build two tables for hospitals and a third containing

the linkage. Build a unique column into all three, with the third having that column as a primary key. Use software to write in a guaranteed unique character/integer value *maybe take system time and run it through some randomizer*.

Third table would be

unique key / hosp 1 / hosp 2 / shared goods

Then use something like Select * from hosp 1 where select hosp2 from table3 where unique key in table1.

I dunno, been awhile :7

[BlueEyedSon]

19. Try this

REL_TAB
------------------------
REL_ID unique key
REL_TYPE required
REL_NAME optional
(other relationship attribs)


HOSP_REL_TAB
------------------------
HOSP_REL_ID unique key
REL_ID foreign key
HOSP_ID foreign key


HOSP_TAB
------------------------
HOSP_ID unique key
(other hospital attribs)


select * from HOSP_TAB
where REL_ID in
(select REL_ID
from HOSP_REL_ID
where HOSP_ID = $1)


Notes:

you can screen on REL_TYPE in the subquery if you need to (otherwise you get all regardless of type)

you may want to exclude the hospital in question in the outer query with a not equal predicate

the HOSP_REL_TAB should have a constraint that the combo of REL_ID and HOSP_ID must be unique

you can reproduce your many-to-many table by a self join on HOSP_REL_TAB.REL_ID

The *secret* is to code a table relating hospitals to their relationship, not directly to each other.

on edit:
recursive equijoin works too... what was I thinkin'?

select A.whatever from HOSP_TAB A, REL_TAB B , REL_TAB C
where B.REL_ID = C.REL_ID
and A.HOSP_ID = B.HOSP_ID
and C.HOSP_ID = $1

Once again, if you are looking for a specific group of hospitals (sharing staff or whatever) you will have to have a predicate on the REL_TYPE or REL_NAME (if known).

And no, the platform is not a factor.

[BlueEyedSon]

22. kick