In a bug/bug-status model, where would the foreign key reside?

Question

I always get stuck on foreign keys so I have a question about where the foreign key should reside in a bug tracking system in which a bug has a single status at any given time while only a small number of statuses exist (Open, Under Investigation, Resolved, Pending Approval). So each status has many bugs associated with it. My assumption is the foreign key should reside in the Bug table as a status_id column referencing the id column in the Status table. Is this a safe assumption?

TABLE:
    Bug
    id integer
    desc string
    status_id integer fk

    Status
    id integer
    desc string

RAILS MODEL:
    Bug
    has_one :status

    Status
    has_and_belongs_to_many :bugs

Answer 1

Yes you are correct in that assumption. As long as each Bug will only have a single Status, you can just include the foreign key to that table.

Answer 2

Yes, this would be correct. The way to think of this would be that your bug has a status; there are many bugs with any given status.

Answer 3

You're correct in your assumption, more importantly then the relationship (One-Many/One-One/Many-Many) is deciding which table is the Primary Key table and which is the Foreign Key table?

In this case the Status table clearly contains the primary key of the FK relationship here. If it was the other way around, then each status would have to exist in the Bug table before it could exist in the Status table, which clearly isn't intended.