Re: SQL Relational tables theory

Top Page
Attachments:
Message as email
+ (text/plain)
Delete this message
Reply to this message
Author: Craig White
Date:  
To: Main PLUG discussion list, derek
Subject: Re: SQL Relational tables theory
On Mon, 2006-01-30 at 23:59 -0700, Derek Neighbors wrote:
> 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. :)

----
I did post on rails list but I was thinking in general SQL setup and I
have been working through the magic mojo. My other thinking is that the
db modeling is important to get right.

Let me clarify a bit since my natural language (not putting in specific
PostgreSQL or ruby terminology) is probably as unclear as my thinking
generally is...

I create a client and a placement virtually simultaneously since a
client must be 'placed' into the system. The client may be discharged -
thus his placement would have an exit date. He may come back into the
system and thus have another placement but I intend to retain his
earlier placement record but his client record would be linked to his
new placement. I am thinking this is the methodology since I would want
to keep track of each client placement into each facility.

I have used db's like Filemaker which I have gained a lot of experience
with the usage and limitation but not so with any SQL db/tables.

My question stems from not knowing the impact of frequent
listings/reports/etc. that would have clients by facility when my
relationships would be...

Clients < - > Placements < - > Facilities

I am wondering if I should have an additional relationship and make it
like this...

Clients < - > Placements < - > Facilities
  |                                |

|----<------------------------->-|

Which might be workable in rails voodoo or might require some extra mojo
to make voodoo but I am trying to achieve a good relational db model
which I have to believe transcends the voodoo of the moment (rails)
since it is possible/likely that the client tools will evolve.

Thanks

Craig

---------------------------------------------------
PLUG-discuss mailing list -
To subscribe, unsubscribe, or to change you mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss