Complicated polymorphic relation

Hi,

I need to do a polymorphic association but i want to save the field
XXX_type in an other table, like that :

Table: USERS
- id
- name

Table: CITY
- id
- name

Table: STATES
- id
- name

Table: MANDATES
- id
- name
- place_type = city or states

Table: ELECTED
- id
- user_id
- mandate_id
- place_id -> id of CITY.id or STATES.id depending on
MANDATES.place_type related by mandate_id

How can I do that with rails ??
I hope you understood me, if not, tell me I can try to explain.
Sorry for my english !

Adrien

Adrien Nom wrote:

Hi,

I need to do a polymorphic association but i want to save the field
XXX_type in an other table, like that :

[...]

Table: ELECTED
- id
- user_id
- mandate_id
- place_id -> id of CITY.id or STATES.id depending on
MANDATES.place_type related by mandate_id

How can I do that with rails ??

Why do you want to do it that way? It seems like extra work for no good
reason. Worse, it's poor DB design. Since place_id and place_type are
so intimately related, they belong in the same table. In this case that
probably means that place_id should go in mandates.

[...]

Adrien

Best,

I want to do that way because i must save the possible mandates in one
table. Then i use an other table to save the relation between mandates
and users.
How can i do that with an other way ?
thanks for your answer
adrien

Adrien Nom wrote:

I want to do that way because i must save the possible mandates in one
table.

That's not a good rationale. The place_id is part of the mandate just
as much as the olace_type is, so it belongs in the mandates table.

Then i use an other table to save the relation between mandates
and users.
How can i do that with an other way ?

If I understand you correctly, this will be very easy with your current
schema, once you make the change I am proposing.

Basically, place_id is a property of the mandate, so it belongs in the
mandates table. Since you have mandate_id in elected, you can get the
place_id with something like @elected.mandate.place_id.

thanks for your answer
adrien

Best,

this solution doesn't fit with my project

i have to explain :

the table MANDATES contain the list of possible mandates like :
(0,mayor,city)
(1,deputy mayor,city)
(2,minister,state)
(3,king,state)
this list use by me to make a form where the user of my program can
select the mandate related by user

the table ELECTED contain the relation between user and mandate like :
(0,John Smith,mayor,Chicago)
(1,Brad X,mayor,New York)
(2,Adrien Y,king,Brasil)
(3,Anton Z,minister,Great Britain)
NB: i replace user_id,mandate_id,place_id by user.name,
mandate.name,place.name

the solution which i see to do that are :
1)i have explain in the first post
2) duplicate the field place_type in the table ELECTED (maybe it is
the easier solution?)
3) group tables MANDATES and ELECTED like that :
MANDATES
- id
- name
- place_id
- place_type
- user_id
in that way i create all possible mandates with no user related to
make the form and when i have a relation between user/mandate i create
a copy of the mandate with the user_id

what do you think about this solution?

thanks

adrien

Maybe my post isn't understandable ?
I have to make this software and I'm trying to find a solution long
time ago but unfortunately my search didn't give me any answer :S

adrien

Adrien Nom wrote:

Maybe my post isn't understandable ?

[...]

Maybe you shouldn't be bumping your posts only 18 hours after the last
time you posted. I know I've been looking at your example and will post
when I have something figured out. Others are probably doing likewise.

But be a little patient and try not to bump the thread so readily --
people don't take kindly to it.

Best,

I'm sorry, I didn't post that to bump my post. It's just because I
know my English level is very low and I don't know if my post is
understandable, if not I can ask friend to help me for explain what I
want. In that way, you could say me and i will do, else I wait an
answer and don't bump my post anymore.

adrien

Adrien Nom wrote:

I'm sorry, I didn't post that to bump my post. It's just because I
know my English level is very low and I don't know if my post is
understandable, if not I can ask friend to help me for explain what I
want.

Then I'm sorry for having responded in the way I did. Yes, it looks
like your description is comprehensible, but thanks for checking.

(Excessive bumping is a pet peeve of mine, so I may well have
overreacted.)

Best,

First, try not to worry so much about how the tables are set up, you
can create a view to the data to suit your needs. What you need to be
concerned about is how to make the information easy to use and to not
repeat yourself.

As to modeling the data - It seems that the User table would be the
central piece and he or she would have one mandate or perhaps more?
Either has_one or has_many.

It would also seem the user has a location as well as mandate. Another
one to one relationship here and again, the user may have more then
one location - has_one or has_many

Model User
id
name
etc.
Has_one: mandate
has_one: location

Model Mandate
id
user_id
etc.
belongs_to :user

Model Location
id
user_id
etc.
belongs_to :user

Then you could have a look up table for the types of mandates and
locations.

Hope this helps

Thanks but it's not a good solution for my problem : one user can have
several mandates for several various locations !
For example :
table ELECTED
(2,Adrien Y,king,Brasil)
(4,Adrien Y,mayor,Rio)

In the first case, the location is a Country and in the second the
location is a City then i think i can't do that with your solution,
i'm wrong ?
adrien

So the way I understand this is that the mandates table is really
providing the valid combinations of place type and position, so that
you can be the king of a state, or the mayor of a city, but not the
mayor of a state, or the king of a city.

If I were doing this, I'd just have the mandate table (possibly with a
different name) hold the valid combinations, and use it to populate a
select lists for office_name and place_time attributes in an object
representing that a user holds a particular office in a particular
place. I'd probably name this Office instead of Elected since some
offices like Mayor are held by election, while others such as king are
not.

And I'd probably have a validation method in Office which ensured that
there really was a mandate with the given office_name and place_type.

Table: USERS
- id
- name
has_many :offices

Table: CITY
- id
- name
:has_many :offices, :as => :place

Table: STATES
- id
- name
:has_many :offices, :as => place

Table: MANDATES
- id
- name
- place_type = city or states

Table: OFFICES
- id
- user_id
- office_name
- place_id
- place_type #copied from the mandate when the office is created.
:belongs_to :user
:belongs_to :place, :polymorphic => true

Yes, it's a good way !
Thanks very much for your help.
Adrien