What would be the appropriate model association?

Question

I have an Organization and Actioncode model. When signing up for a premium account, an organization can enter an actioncode for a discount. Hence, an organization has used no or 1 actioncode, while an actioncode can be used by many organizations.

Current association:

Organizations migration file:  t.references :actioncode,   index: true,    foreign_key: true
Organization model:            belongs_to :actioncode
Actioncode model:              has_many :organizations

Initial problem with this setup: actioncode_id should not be mandatory On seeding I used to get the error code ActiveRecord::AssociationTypeMismatch: Actioncode(#23575660) expected, got Fixnum(#5494220). The cause seemed to be that actioncode_id had become a mandatory column in the Organization model. This should not be the case; actioncode should also be able to be nil as an organization may also not use/have any actioncode when paying for a premium account.

Current situation: After upgrading to PostgreSQL: In response I've upgraded my db to PostgreSQL. Nevertheless, now on migration I get the error message (see full message at bottom of post):

PG::UndefinedTable: ERROR: relation "actioncodes" does not exist

The error message: Any ideas what is causing this error? Removing the three lines under 'current association' and I have no problems with migration and seeding. In fact, keeping all three lines but removing index: true, foreign_key: true from the first line and it also migrates correctly. When I keep index: true on migration it gives the error: SyntaxError: xxx_create_organizations.rb:17: syntax error, unexpected tSYMBEG, expecting => t.boolean :activated. When I keep foreign_key: true it produces the original error message PG::UndefinedTable.

And if I change the first line to the incorrect code of: t.references :actioncode_id, index: true, foreign_key: true it gives as the following error:

PG::UndefinedTable: ERROR:  relation "actioncode_ids" does not exist
: ALTER TABLE "organizations" ADD CONSTRAINT "fk_rails_604f95d1a1"
FOREIGN KEY ("actioncode_id_id")
  REFERENCES "actioncode_ids" ("id")

So given _ids on the last line, Rails somehow does seem to have problems with the name of the table. Adding self.table_name = 'actioncodes' to the actioncode model file makes no difference. What to do?

---

Alternative associations:

I wonder if I need an alternative association to meet my need. I've looked into several other associations but am unsure what to do. Best case scenario I just have a column in the organization model that includes the used actioncode or is nil if no actioncode was used. I need a model association to for example print all the organizations that have used a specific actioncode.

Alternative association 1: Problem with this association is that actioncode remains a mandatory variable in the organization model and thus cannot be nil.

Organization migration:  t.references :actioncode,   index: true,    foreign_key: true
Organization model:      has_one :actioncode
Actioncode model:        has_many :organizations

Alternative association 2: This association would hold the organization_id in the actioncodes table (I guess as an array), while from my perspective it would make much more sense to include the actioncode_id in the organization table. Also, the code below still expects an actioncode for each organization.

Organization model:          has_one :actioncode
Actioncodes migration file:  t.belongs_to :organization, index: true

Alternative association 3: I've also looked at creating an additional association model but it makes me wonder if I'm not overthinking things when I would follow this strategy, as this would add an extra table/model. Also, I'm unsure if the organization model below wouldn't still require a value for discount, thereby not really solving the original problem. But if this would be the way to go, I was thinking of:

Organization model:    has_one :discount
                       has_one :actioncode, through: :discount
Discount model:        belongs_to :organization
                       has_one :actioncode
Actioncode model:      belongs_to :discount
Discount migration:    t.belongs_to :organization, index: true
                       doesn;t need any other columns
Actioncode migration:  t.belongs_to :discount, index: true

Alternative association 4: Lastly, the has_and_belongs_to_many Association didn't make sense to me since how I understand it that's intended for a many:many relationship, while organization:actioncode is a many:1/0 relationship.

What setup should I use and doesn't have actioncode as a mandatory variable for an organization?

---

Additional information: The SQL from running rake db:migrate after rake db:drop and rake db:create):

== 20150410153815 CreateUsers: migrating ===============================
-- create_table(:users)
   -> 0.0582s
== 20150410153815 CreateUsers: migrated (0.0628s) ======================

== 20150410200022 AddIndexToUsersEmailAndUsername: migrating ===========
-- add_index(:users, :email, {:unique=>true})
   -> 0.0109s
-- add_index(:users, :username, {:unique=>true})
   -> 0.0100s
== 20150410200022 AddIndexToUsersEmailAndUsername: migrated (0.0219s) ==

== 20150416113853 CreateOrganizations: migrating ==============================
-- create_table(:organizations)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR:  relation "actioncodes" does not exist
: ALTER TABLE "organizations" ADD CONSTRAINT "fk_rails_4ecaa2493e"
FOREIGN KEY ("actioncode_id")
  REFERENCES "actioncodes" ("id")
/usr/local/rvm/gems/ruby-2.1.5/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'
<<SNIP>>
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "actioncodes" does not exist
: ALTER TABLE "organizations" ADD CONSTRAINT "fk_rails_4ecaa2493e"
FOREIGN KEY ("actioncode_id")
  REFERENCES "actioncodes" ("id")
/usr/local/rvm/gems/ruby-2.1.5/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'
<<SNIP>>

Additional information: The SQL from the migration running the current association (running rake db:drop and then rake db:setup):

-- enable_extension("plpgsql")
   -> 0.0664s
-- create_table("actioncodes", {:force=>:cascade})
   -> 0.0412s
-- add_index("actioncodes", ["actioncode"], {:name=>"index_actioncodes_on_actioncode", :unique=>true, :using=>:btree})
   -> 0.0217s
-- create_table("organizations", {:force=>:cascade})
   -> 0.0237s
-- add_index("organizations", ["summ"], {:name=>"index_organizations_on_summ", :unique=>true, :using=>:btree})
   -> 0.0106s
-- add_index("organizations", ["name"], {:name=>"index_organizations_on_name", :unique=>true, :using=>:btree})
   -> 0.0197s
<<SNIP, OTHER TABLES >>
-- initialize_schema_migrations_table()
   -> 0.0352s
-- enable_extension("plpgsql")
   -> 0.0405s
-- create_table("actioncodes", {:force=>:cascade})
   -> 0.0176s
-- add_index("actioncodes", ["actioncode"], {:name=>"index_actioncodes_on_actioncode", :unique=>true, :using=>:btree})
   -> 0.0085s
-- create_table("organizations", {:force=>:cascade})
   -> 0.0148s
-- add_index("organizations", ["summ"], {:name=>"index_organizations_on_summ", :unique=>true, :using=>:btree})
   -> 0.0113s
-- add_index("organizations", ["name"], {:name=>"index_organizations_on_name", :unique=>true, :using=>:btree})
   -> 0.0195s
<<SNIP, OTHER TABLES >>
-- initialize_schema_migrations_table()
   -> 0.0342s
rake aborted!
ActiveRecord::AssociationTypeMismatch: Actioncode(#39911240) expected, got Fixnum(#20092360)
/usr/local/rvm/gems/ruby-2.1.5/gems/activerecord-4.2.1/lib/active_record/associations/association.rb:216:in `raise_on_type_mismatch!'
<<SNIP >>

Answer 1

Based on, "... an organization has no or 1 actioncode and an actioncode can be used by many organizations", your modelling is correct, it's just that the foreign key that has been created by the migration does not allow nulls in the actioncode_id column. You might check the migration SQL to see what command is used.

Maybe the database you're using doesn't allow nil foreign key columns, but most do. You might have to construct the foreign key with SQL instead of with Rails in order to allow nulls.

From the postgresql docs: http://www.postgresql.org/docs/9.3/static/ddl-constraints.html

Now it is impossible to create orders with non-NULL product_no entries that do not appear in the products table.

Note, "... with non-NULL product_no entries ...", null entries should be fine, and your modelling is correct.

Answer 2

You have a logic/flow problem here.

Organization shouldn't belong to Actioncode, if anything an Organization can own one code. Or not. That's why

Organization

has_one :actioncode

Actioncode

belongs_to :organization

That way Organization won't hold the actioncode_id, but Actioncode will hold organization_id.

UPDATE:

Do as I mentioned above and make Actioncode posess a type or value field which would contain a 'AC-08821' string for example. That way you could maintain the relations expected between those two models and still be able to e.g. search for all codes with the same value.

OR

Create an association table, for example using has_and_belongs_to_many

More info here: http://guides.rubyonrails.org/association_basics.html#choosing-between-has-many-through-and-has-and-belongs-to-many

Basically you create another table which represents a connection between the two models.