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