SQL Relational tables theory

[Derek Neighbors]

Craig,

Craig White wrote:
> I have a number of tables but specifically...
>
> clients
> placements
> facilities
>
> A client may have only 1 placement
> A placement may have only 1 client but there may be many placements per
> client.
>   
I'm confused. 
A client may have only 1 placement. 
 AND
There may be many placements per client.

Appear to be opposing statements.  Which is it?  Can a client have one 
placement or many placements?  or is it too late and I am misreading this?
> A placement may have only 1 facility but there may be many placements
> per facility
> A facility may have only 1 placement but there may be many placements
> per facility
>   
Again confusion.  How can a they only have 1 placement, but maybe many?
> I'm wondering if it is necessary to store a facility relation in the
> client table since the client will have one relation to the placement
> and the placement will have a one relation to the facility.
>
> I do anticipate needing frequent reports that list clients per facility
> so that would be an advantage to store the facility in the client.
> Should I be creating a relationship between client and facility when the
> real relationship is between client and placement and the other real
> relationship is between placement and facility?
>   
The natural language used makes this confusing, but if you really need 
the many relationships you need to create additional tables...

clients
placements
facilities
client_placements
facility_placements

client_placements
-----
id
client_id
placement_id

facility_placements
----
id
facility_id
placement_id

Again... it's past my bedtime so maybe I misread this.  I noticed you 
posted on the rails list.  Know that ActiveRecord has some magic mojo to 
make these relationships VERY transparent. :)

--
Derek Neighbors
Integrum Technologies
http://www.integrumtech.com
"Redefining IT"