OK, for someone who hasn't studied database normalisation, you're doing well. That is to say, you have the right idea the way you have separated things into the three tables.
There is only one detail you will need to change to solve your problem.
You primary key(s) in your Home_Ops table is wrong (at least, not the best). Your Home_ops table should consist of only 2 columns: home_id, and option_id.
The primary key needs to be set on the COMBINATION of the two foreign keys! (references)
<pre>
CREATE TABLE Homes (
id INT PRIMARY KEY,
price INT NOT NULL
);
CREATE TABLE Options (
id INT PRIMARY KEY,
option_text TEXT
);
CREATE TABLE Home_Ops (
home_id INT REFERENCES Homes (id),
option_id INT REFERENCES Options (id),
PRIMARY KEY (home_id,option_id)
);
</pre>
Of course, you could solve your problem by simply using the DISTINCTROW keyword after select but that is not really a solution for your particular problem. The above method would be better by design.
I haven't test the above SQL, but it's how PostgreSQL does it. Check your user manual to verify the syntax if you use a different database. I recommend PostgreSQL 🙂
